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