RE: sql Vs RLA to test existance

Bad idea for performance. Why count all the rows just to discover
whether 1 row exists ?

Better would be...

Select 1 into :X from ???? Where myfield = 'myfield' Fetch First Row
Only

Or, using exists...

Select 1 into :X From SYSIBM/SYSDUMMY1 Where Exists (Select 1 from
mytable Where myfield = 'myfield')



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Marc Couture
Sent: 15 February 2011 14:55
To: RPG programming on the IBM i / System i
Subject: RE: sql Vs RLA to test existance


What about

Select count(*) into :X from ???? myfield = 'myfield') ?


"If you think education is expensive, try ignorance"
(Derek Bok, pres. of Harvard Univ. 1971-1990)

From: David.FOXWELL@xxxxxxxxx
To: rpg400-l@xxxxxxxxxxxx
Date: Tue, 15 Feb 2011 15:44:30 +0100
Subject: sql Vs RLA to test existance

Hi,

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')

Then again, I seem to remember that EXists will scan all the rows and

not just stop at the first one that satisfies the search criteria as I
could do with RLA.

Can someone put me straight please?

Thanks.

--
This is the RPG programming on the IBM i / System i (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.