RE: Embedded sql how to test for no lines

Why not simply:

Clear YourVar;
select 1 into :YourVar
from YourSchema.YourTable
Where ...
Limit 1;
If YourVar > *Zeros --> Found!


Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"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!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Tuesday, 27 August 2024 20:32
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Embedded sql how to test for no lines

Hi Brian,

your missing the from clause:

select '1'
into :existsFlag
from sysdummy1
where exists (select ...);

But I think you can get it even faster and easier:

select char(count(1))
into :existsFlag
from <your table>
where <your conditions>
limit 1;

This way you don't have to observe sqlcode=100 - because count() never emits 100. As the select is limited to 1 row, you can only get values 1 and 0 - and those are converted to char - and then to IND.

This is the tactic I use very often in my programs.

HTH
Daniel

Am 27.08.2024 um 19:18 schrieb Brian Garland via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

If memory serves me correctly EXISTS is more efficient than COUNT.

DCL-S ExistsFlag IND;
ExistsFlag = '0'
SELECT '1' INTO :ExistsFlag WHERE EXISTS(SELECT......)

Brian

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.