× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.