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

[Pete Helgren <Pete@xxxxxxxxxx>]

Dave,

Others may have a better approach, but I would normally construct the 
SQL string as a variable itself and insert the file name as a variable 
in the string.  In your case, you have specified in your SQL that the 
statement should be run over a file named AUDITFILE.  You might try a 
host variable :AUDITFILE but I am not sure that would work.  I would do 
something like (pseudocode) :

// Build the SQL statement  
AuditFile = 'MyfileName' ;

gSQLStmnt = 'Select * from ' + AuditFile + ' Where CRDY =  ' + RunDate 
+' Order by SEQU for Fetch Only';

PREPARE S1 FROM :gSQLStmnt;

DECLARE C1 CURSOR FOR S1;

Open C1;

You'll have to build the SQL statement as a static string, including 
your date as a non-host variable, and then pass the whole thing as a 
variable to your prepare and cursor.

There might be a better approach but this is how I'd go about it.

Pete Helgren




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
>
>                               
>
>