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