|
midrange.com
Rick, FYI, the '1' option is good, but I hope that you are initializing
the field before executing the SQL statement. I've come across programs
that didn't clear the "into" fields before hand and when the SQL statement
didn't return anything, the "into" field contained the result of the
previous execution. But that was on a previous version, I haven't tried it
on V5R3 or later yet. And I'm sure you're already doing that. :-))
Michael Schutte
Work 614-492-7419
email michael_schutte@xxxxxxxxxxxx
"rick baird"
<rick.baird@gmail
.com> To
Sent by: "RPG programming on the AS400 /
rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
04/18/2006 02:56 Re: SQL to only check for
PM existence:
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
FYI, final results:
I tried both ways ('1' and IBMREQD) for my result set with no
degredation in performance either way, so I left it as '1' so that I
could load an indicator field for asthetic reasons. ( if not
$itemfound)
Using EXISTS cut the response time from about 20-30 seconds to 6-10
seconds on a LIKE compare similar to this: '_____ABC_DEFG' where
the first record that matched was near the end of the access path -
1011AAABC1DEFG'
not great, but a lot better and acceptable.
thanks again,
Rick
On 4/18/06, rick baird <rick.baird@xxxxxxxxx> wrote:
> Charles and Michael,
>
> I see now that you are correct. (never should have doubted) my way
> returned more than one record, and bombed (which could have been
> corrected by a fetch 1, but who wants to do that?)
>
> It's interesting that they'd put a dummy file with a dummy record out
> in sysibm for that very reason. Handy, but looks like a kludge to
> overcome a weakness.
>
> Thanks to both of you,
>
> Rick
>
> On 4/18/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx>
wrote:
> > Rick I think between all the available answers I believe that Charles
is
> > right.
> >
> > Do this logic, pulled from another user.
> >
> > myInd = 'N';
> >
> > Select IBMREQD into :myInd from sysibm/sysdummy1 where exists (Select 1
> > from myfile where thekey like '%abc%')
> >
> > If found in the subselect... myInd will have a 'Y', it's the only
> > value/record in sysdummy1.
> >
> >
> >
> > Michael Schutte
> > Work 614-492-7419
> > email michael_schutte@xxxxxxxxxxxx
> >
> >
> >
> > "Wilt, Charles"
> > <CWilt@xxxxxxxxxx
> > om>
To
> > Sent by: "RPG programming on the AS400 /
> > rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
> > midrange.com
cc
> >
> >
Subject
> > 04/18/2006 01:02 RE: SQL to only check for
> > PM existence:
> >
> >
> > Please respond to
> > RPG programming
> > on the AS400 /
> > iSeries
> > <rpg400-l@midrang
> > e.com>
> >
> >
> >
> >
> >
> >
> > Using EXISTS is the best why to do what you're asking for.
> >
> > EXISTS is specifically designed to answer the question you are asking
> > and by using it the query engine knows exactly what you are looking
for.
> >
> > You may get performance as good sometimes with Michael's method, but
you
> > will never get better and you could get worse.
> >
> > EXISTS is also part of the SQL standard.
> >
> > I do wish IBM would allow something like so:
> > c/exec SQL
> > c+ VALUES ( exists (select '1' from MYFILE where THEKEY =
> > 'SOMEKEY'))
> > c+ into :myFlag :nullInd
> > c/end-exec
> >
> > c if %nullInd
> > // record does not exist
> > c else
> > // record exists
> > c endif
> >
> >
> > HTH,
> >
> > Charles Wilt
> > --
> > iSeries Systems Administrator / Developer
> > Mitsubishi Electric Automotive America
> > ph: 513-573-4343
> > fax: 513-398-1121
> >
> >
> > > -----Original Message-----
> > > From: rpg400-l-bounces@xxxxxxxxxxxx
> > > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rick baird
> > > Sent: Tuesday, April 18, 2006 11:25 AM
> > > To: RPG programming on the AS400 / iSeries
> > > Subject: Re: SQL to only check for existence:
> > >
> > > Thanks Charles - very similar to Michael's answer, but with
> > > another layer.
> > >
> > > If I get performance problems where the first match is way down in
the
> > > access path, I'll try this too.
> > >
> > > at this point, the select without the exists() looks like a winner.
> > >
> > > On 4/18/06, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> > > > Check out the EXISTS predicate.
> > > >
> > > > Select 1
> > > > >From sysibm/sysdummy1
> > > > Where exists (select 1 from my where myfield like '___abc___')
> > > >
> > > > HTH,
> > > >
> > > >
> > > > Charles Wilt
> > > > --
> > > > iSeries Systems Administrator / Developer
> > > > Mitsubishi Electric Automotive America
> > > > ph: 513-573-4343
> > > > fax: 513-398-1121
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: rpg400-l-bounces@xxxxxxxxxxxx
> > > > > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rick baird
> > > > > Sent: Tuesday, April 18, 2006 10:56 AM
> > > > > To: Midrange Systems Technical Discussion; RPG programming on
> > > > > the AS400 / iSeries
> > > > > Subject: SQL to only check for existence:
> > > > >
> > > > > hey all,
> > > > >
> > > > > Is there a way to run an SQL statement that doesn't (necessarily)
> > > > > return anything, but only checks for the existence of one or more
> > > > > records based on a where statement - similar to a SETLL
> > > and %found?
> > > > >
> > > > > I've got a rather complicated SQL statement using:
> > > > > LIKE '___abc___' or it could be:
> > > > > LIKE ______xyz'
> > > > >
> > > > > - over a very large file, and I'm having performance issues.
> > > > >
> > > > > I don't want to do a count(), because I don't care how
> > > many, only that
> > > > > at least one exists. with count, it would have to read
> > > through the
> > > > > entire file to determine it.
> > > > >
> > > > > I also tried just doing a select - optimize for 1 row and a
single
> > > > > fetch, but that seemed to take forever too. It appears
> > > as if it is
> > > > > searching the entire file, instead of stopping at the first one.
> > > > >
> > > > > help!
> > > > >
> > > > > Rick
> > > > >
> > > > > --
> > > > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
> > > > > mailing list
> > > > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > > > To subscribe, unsubscribe, or change list options,
> > > > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > > > Before posting, please take a moment to review the archives
> > > > > at http://archive.midrange.com/rpg400-l.
> > > > >
> > > > >
> > > >
> > > > --
> > > > This is the RPG programming on the AS400 / iSeries
> > > (RPG400-L) mailing list
> > > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives
> > > > at http://archive.midrange.com/rpg400-l.
> > > >
> > > >
> > >
> > > --
> > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
> > > mailing list
> > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/rpg400-l.
> > >
> > >
> >
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
> >
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
>
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
