Re: sql Vs RLA to test existance

[CRPence <CRPbottle@xxxxxxxxx>]

On 2/15/11 6:44 AM, David FOXWELL wrote:
> I need to find if a transaction of a particular type and amount
> exists for a client.
>
> I cannot remember how to apply the SQL Exists( ) to look for the
> transaction.
>
> Select '1' from ????
> where exists ( select * from mytable
>                where myfield = 'myfield')
>
> <<SNIP>>

    select '1' /* or e.g. left(char(count(*)),1) */
    from qsys2/qsqptabl /* sysdummy1 equivalent  */
    where exists (  select '1'
                    from mytable
                    where TransType=:TT
                      and TransAmnt=:TA  )

  If there is an INDEX with TransType and TransAmnt as [the leftmost] 
keys, then the access could be "index-only" against the "mytable" data 
to validate existence.  That SQL would perform almost as fast or even 
faster than a SETLL across repeated requests [since building the access 
plan and first open], for various replacement values for the 
transaction-type and transaction-amount variables.

Regards, Chuck