Re: AW: SQL Question -- RPG400-L

The best way is what Birgitta suggested, and you check your SQLCODE = 0 or the SQLSTATE for a normal, found value.

The "Fetch First Row Only" clause is, like, if you only want to check existence for the record, there is every reason to use it. You find one, stop the search right now. Why not save some nanoseconds even if it's for a 100-row file?

If you want to use the value later in embedded SQL or in an SQL procedure, you can do this:

Set Exists = 0 ;

Select 1 into :Exists from (Your From or Join list, your conditions etc.)
Fetch First Row Only

Declare Exists as any number (I'd use Integer).
For an SQL procedure of course remove the colon from :Exists.

--Alan

On 12/19/12 11:50 PM, Birgitta Hauser wrote:

If you only want to know if a record exists, the following query might be
the best one:

Select 1
>From /**** specify your tables/views/files and join conditions *****/
Where /**** specify your conditions ******/
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: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Jeff Young
Gesendet: Wednesday, 19.12 2012 21:49
An: RPG programming on the IBM i / System i
Betreff: Re: SQL Question

Thanks.
I knew that there was an easy way, but could not think of it.

On Wed, Dec 19, 2012 at 3:39 PM, Alan Shore <ashore@xxxxxxxx> wrote:

Another way is
Select count(*) from file where value = whatever

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

-----Original Message-----
From: Alan Shore
Sent: Wednesday, December 19, 2012 3:38 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Question

Select * from file where value = whatever

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Jeff Young
Sent: Wednesday, December 19, 2012 3:29 PM
To: RPG programming on the IBM i / System i
Subject: SQL Question

All,
I am having a brain freeze (aka senior moment).
I want to use SQL to determine if a record exists in a file for a
given value.
There will be either only 1 record in the file for the value, or it
will not exist.

Thanks,

--
Jeff Young
Sr. Programmer Analyst
--
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.

Disclaimer: This message contains confidential information and is
intended only for the individual named. If you are not the named
addressee you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system. E-mail
transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late

or incomplete, or contain viruses.

The sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a result of
e-mail transmission. If verification is required please request a
hard-copy version. Any views or opinions presented are solely those of
the author and do not necessarily represent those of the company.
--
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.

--
Jeff Young
Sr. Programmer Analyst
--
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.