RE: SQL Existence Check

["Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>]

>From: Frank.Kolmann@revlon.com [mailto:Frank.Kolmann@revlon.com]
>I am also looking for a SQL equivalent to CHAIN or SETLL.
>This is what I am using now, is there a better way. :)

Wow, you're pulling up old messages! What version of the OS? If you're on
V5R1 (I don't think it was in V4R5) there is a FETCH FIRST clause on the
select. If the boys in Rochester have done their job that will give the
optimizer what it needs to only look for one row and if you don't order the
result set you should be rather close to the exists predicate.

I would say the new code would be:
C/EXEC SQL
C+ SELECT 1 into :DUMMY  from   CUSTFILE
C+ WHERE CUSTNO = :CUSTNUM FETCH FIRST ROW ONLY
C/END-EXEC
C                   If        SQLCOD = 0
C                   Eval      FOUND = 'Yes'
C                   EndIf

I used "select 1" instead of selecting a field because there is no need to
bring back the data and this should always make the select a covered query,
that is any index will satisfy the data selection requirements and it's
unlikely the query engine will ever look at the physical, just the index.
I'm not sure if "SELECT 1 FROM ..." would work (no "into :DUMMY") but that
would even optimize more since there would be NO data returned from the
result set at all.

-Walden

------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@TechSoftInc.com
http://www.TechSoftInc.com