Re: Variable file names in Select from clause in SQPRPGLE?

Hi Dave

Yes you can, I use variable file names in my code/description prompt routines. You have to make use of the SQL "PREPARE" statement though. For example:

C* Build the SQL statement... C Eval wSqlStmt = 'SELECT ' + %Trim(PRFIELD) +
C ', ' + %Trim(PRTXTFLD) + ' FROM ' + C %Trim(PRFILE)
C* Time to start preparing and opening the file cursor...
C/Exec SQL C+ Prepare prpSQL C+ From :wSqlStmt C/End-Exec C* Declare the cursor... C/Exec SQL C+ Declare csrSQL Cursor C+ For prpSQL C/End-Exec C* Open the cursor... C/Exec SQL C+ Open csrSQL C/End-Exec

All the best

Jonathan

Dave Murvin wrote:

I have a situation where I would like to run an embedded RPGLE SQL over
an audit database file where the actual file name can change. The audit
files are created by a 3rd party programs and I just have to use what
they give me. I have to go through a couple of files to determine the
current file name and library (library does not usually change except
between environments). File names are typically M3DJD00002, M3DJD00005,
Etc. depending on the file being audited.


I have tried variations of the following, but can not seem to make it
work. Can the file names be variables?


AuditFile = DBXLIB + '.' + DBXFIL;


/End-Free

C/EXEC SQL

C+ Declare C1 Cursor for

C+ Select * from AUDITFILE

C+ Where CRDY = :RunDate

C+ Order by SEQU

C+ for Fetch Only

C/END-Exec


Position 26 Column definitions for table AUDITFILE in
*LIBL not found.

I have also tried

C+ Select * from :DBXLIB.:DBXFIL

Token : was not valid. Valid tokens: (TABLE LATERAL <IDENTIFIER>


C+ Select * from :DBXLIB/:DBXFIL

Token : was not valid. Valid tokens: (TABLE LATERAL <IDENTIFIER>



Just to make it easier, if someone adds a field to be audited, the file
definition is changed and my fetch into a data structure is likely to
get a level check.


Any suggestions?


Thanks


Dave