RE: sql Vs RLA to test existance

I don't disagree that RLA would be better rather than SQL EXISTS.

But whether a table scan is done depends on whether the engine THINKS this would be the faster approach (is there an index on myfield, how my rows on the table etc.). Are you sure it is a table scan on mytable or a table scan on SYSDUMMY1 ?



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


Thanks, I didn't remember the sysdummy and I couldn't think how to use exists with only one table.

Sorry to disappoint, but if you run your requests with visual explain you will see that the whole table gets scanned.
That's why finally I'm sticking with RLA.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de McGovern, Sean
Envoyé : mardi 15 février 2011 16:16
À : RPG programming on the IBM i / System i
Objet : 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.

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

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