RE: Embedded SQL - Variable File Name

["Holden Tommy" <Tommy.Holden@xxxxxxxxxxxxxxxxx>]

No you can't do this.  You'll have to use dynamic SQL.

/free
  my_statement = 'select sum(ioqtyo) into ? ? From '+
        myfile+' where ioitem = ? And iowhse = ''PH'' and'
        +' iodele <>''D''';
/end-free

c/exec sql
C+ prepare mysql from :my_statement
c/end-exec
c/exec sql
C+ execute mysql from :pwkquan :wknull :pwkitem
c/end-exec


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Thursday, December 01, 2005 6:48 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Embedded SQL - Variable File Name

Why can't I do this?

C/Exec SQL         
C+ Select Sum(IOQTYO)
C+   Into :pWkQuan :WkNull
C+   From :MyFile  
C+   Where IOITEM = :pWkItem And
C+         IOWHSE = 'PH'     And
C+         IODELE <> 'D'
C/End-Exec         

Where MyFile is a variable. I get a "Token : was not valid. Valid
tokens: (
TABLE LATERAL <IDENTIFIER>." message.

Rob thought it might be because there wasn't a space before my null
indicator variable, but that didn't seem to fix it. The statement was
syntactically correct (and worked too!) before I tried a variable file
name.

How can I do this? Do I need a prepared statement with parameter
markers?
Anyone have an example?

(Posted to correct list)

Thanks!