Re: How can I retrieve the number of records in a file with SQL.

date: Wed, 2 Oct 2024 06:21:18 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: How can I retrieve the number of records in a file with
SQL.

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)