RE: SQL to only check for existence:

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