If you only want to know how may records are in a file, there is no need to
count the records (may take some time for big tables).
You can get this information from the SYSTABLESTAT view:
Select Table_Name, Table_Schema, Number_Rows
From QSYS2.SysTableStat
Where Table_Name = 'WHATEVER' and Table_Schema = 'SOMETHINGELSE';
Otherwise SELECT ... INTO is not supported for dynamic SQL.
You can DECLARE a cursor in composition with dynamic SQL and then execute
PREPARE, OPEN, FETCH, CLOSE.
Or you can use VALUES ... INTO which can prepared and executed dynamically:
CmdSQL = 'VALIES(SELECT COUNT(*) FROM ' + YOURSCHEMA + '/' + YOUTABLE + ')
into ?';
Exec SQL Prepare DynSQLStmt from :CMDSQL;
Exec SQL Execute DynSQL usint :YourOutputVar;
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: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of a4g
atl
Sent: Tuesday, 1 October 2024 21:20
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: How can I retrieve the number of records in a file with SQL.
I have a program that processes 25 files. The data I need is not static so I
have to use dynamic SQL statements.
Example:
wSqlCmd = 'Select COUNT(*) from Library/' + %trim(file_name); exec sql
execute immediate :wSqlCmd;
How would I be able to do the equivalent of Select count(*) into :wCount
from library/file;
TIA, Darryl Freinkel.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
