Re: SQL record exists

Hi,

Exec SQL  Select 1 Into :Count
                  From MyFile
                  Where CUSNR = :cusnr;

This statement will work if there is exactly one record that satisfies the
condition, but you'll get an negative SQL code (-811) if there is more than
a single row returned.
(Example your Sales table can contain several rows for the same customer. If
you want to find out if a specific customer bought something, you may get
several rows returned.)
To avoid this problem enhance the statement as follows:

Clear IsFound;
Exec SQL  Select 1 Into :iSFound
            From MyFile
            Where ....
         Fetch first row only;

This solution is much faster than determining the number of rows (according
to my last tests around 20 times faster) or using a group by (around 7 times
faster).
(BTW my tests may not be representative, but I repeated the same statement
1000, 10000 and 100000 times in a loop, and I was alone on the machine and
there did not run anything else)
I found out even faster as the previous solution is the following (I cannot
explain why. IMHO it should be slower. And I found nobody who could explain
it):

Clear IsFound;
Exec SQL  Select 1 Into :IsFound
           From SysIbm/SysDummy1
           Where exists (Select 1
                            from MyTable
                            Where ....
                            Fetch First Row Only);

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Thomas Garvey
Gesendet: Friday, 05. February 2010 23:19
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL record exists

I usually use...
Exec SQL  Select 1 Into :Count
                  From MyFile
                  Where CUSNR = :cusnr;

At least it SEEMS like the entire file is not read to actually produce a
correct count.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Friday, February 05, 2010 3:14 PM
To: Midrange Systems Technical Discussion
Subject: SQL record exists

All,

I have a procedure in a service program where I need to find out if a record
exists in a file.  I don't want or need the record, I just need to know if
it exists.

I could define a file in the procedure (V6R1) but then if the file layout
changes, the service program needs recreated, so I thought embedded SQL
would be better.

I could do this:

Exec SQL  Select Count(*)
                  Into :Count
                  From MyFile
                  Where CUSNR = :cusnr;

and then check Count.  I did some googling on 'SQL check record existence'
and found some really interesting discussions on EXISTS and IN and JOINing
and who knows what else.  Some of the examples got fairly complex for what I
would say is a simple thing to do.

CUSNR is the primary key, so Count would either be 0 or 1.  Is there a
generally accepted way to do this in SQL?  Or is it whatever floats your
boat?

As always, for me, readability is far more important than performance.
 This routine will be called 300-600 times a day.

Thanks.

--
Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
www.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of my
company.  Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.