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

You can't use STATIC SQL like that. Instead you need to use DYNAMIC SQL via the PREPARE and EXECUTE
commands.

Look in the manual and in the archives.

HTH,

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Dave Murvin
Sent: Monday, June 16, 2008 7:25 PM
To: RPG programming on the AS400 / iSeries
Subject: Variable file names in Select from clause in SQPRPGLE?

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



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

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.