|
midrange.com
Mike,
thanks, yes I am. never assume. ;)
On 4/18/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx> wrote:
> 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.
>
>
>
> --
> 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.
