|
View previous topic :: View next topic
|
| Author |
Message |
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
In some programs here I read that to test to see if a records already exists they code
| Code: |
select count(*)
into host-variable-counter
from table
where key = ... |
Then they check host-variable-counter being > 0
My question is ~ Is it more efficient to code ?
| Code: |
select key-column
into host-variable-counter
from table
where key = ... |
Then check for SQLCODE = 0 |
|
| Back to top |
|
 |
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi
The First one is better since we are using Key column in the where clause. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Suresh,
How does the WHERE clauses differ in this context? Both the SQLs use the key column in the where clause. |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
True.. Both the queries are using the same WHERE Clause. So WHERE Clause doesn't matter here.
Thanks for correcting me. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
I'd suggest using the first format with count. . .
If there is more than one row that satisfies the where, a non-zero (which does not indicate "row not found") will be returned (-811 iirc) if a column is named rather than count.
The check for sqlcode = zero would not work correctly. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hi Dick,
If he meant the "key column" to be having only unique rows, I guess it'll not make any difference...  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Arun,
Yup, if the key-column represents a single-column, unique key
The "count" format should work for all cases and would not potentially cause additonal work or problems later if the key were expanded to include a second column because the first column could no longer guarantee uniqueness (say some major business change or merger). |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hi Dick,
In similar situations we have used something like this.
| Code: |
select DISTINCT 1
into :host-variable
from table
where key = ... |
and then check for SQLCODE = 0 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Arun,
That would surely work as well
d |
|
| Back to top |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
There was no didfference in the where clause
So the better of the two is ..
| Code: |
Select count(*)
into ws-counter
from table
where key-cloumn = host-var |
then check the counter > 0
Thanx for your help... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
IF you are using DB2 Vsn7 or above:
you only care if the count is 0 or >0
then select literal or select keyfield
with a
FETCH FIRST ROW ONLY
clause,
which will stop the search.
A count will continue to the end of the table ,
or index if your WHERE clause makes it an index scan instead of a table scan.
if you only want 0, 1 or >1
select a field or literal and let the -811 stop the search when it finds the second.
If you need the COUNT, then do count, |
|
| Back to top |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
| Thanks for all the suggestions |
|
| Back to top |
|
|
|
|
