Re: SQL to only check for existence: -- RPG400-L

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.
> >
> >
>