Re: SQL to only check for existence:

["rick baird" <rick.baird@xxxxxxxxx>]

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