Dan,
I tried:
Exec SQL set :TheCount = select count(*) as aCount from File1;
--> Exec SQL Set :TheCount = Select Count(*) .... );
You simply missed the parenthesis around the SELECT-Statement.
The following example works for me:
DCL-S FromDAte Date(*ISO) inz(d'2004-01-01');
DCL-S ToDate Date(*ISO) inz(d'2004-12-31');
Exec SQL Set :NbrRows = (Select count(*)
from File1 u join File2 a
on u.Key1 = a.Key2
Where column1 between :FromDate
and :ToDate);
Dsply NbrRows;
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!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> On Behalf Of Dan
Sent: Freitag, 6. April 2018 22:17
To: RPG400-L@xxxxxxxxxxxx
Subject: Return count of records from SQL select
I must be ready for the weekend, as I can't seem to remember how to do this
in RPG.
I need to know ONLY the number of rows that would be returned from an SQL
select statement. I don't need to know anything about the rows themselves.
The decision I need to make is based on whether the number of rows from the
select statement is zero or greater than zero.
I tried:
Exec SQL set :TheCount = select count(*) as aCount from File1; which gave
me:
SQL0312 30 7 Position 22 Variable THECOUNT not defined or not
usable.
SQL0199 30 7 Position 40 Keyword COUNT not expected. Valid
tokens:
I tried:
Exec SQL select * from File1;
Exec SQL Get Diagnostics :TheCount = Row_Count; which gave me:
SQL0029 30 7 Position 17 INTO clause missing from embedded
statement.
SQL0312 30 8 Position 34 Variable THECOUNT not defined or not
usable.
- Dan
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
