Re: SQL record exists

[Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>]

Do you have to set Count to 0 first?  What happens if there is no row
that matches?

Thanks.

On Fri, Feb 5, 2010 at 5:19 PM, Thomas Garvey <tgarvey@xxxxxxxxxx> wrote:
> 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.