Re: Variable File names in SQL Procedure

On 3/16/11 2:49 PM, Ron Adams wrote:

I am trying to create a stored procedure in iNav that will read a
file on my i5 and update our price file.
This is something I have to do on a somewhat regular basis, and I
was trying to create a stored procedure to help me with it.
I usually receive new price sheets from sales, via a spreadsheet
which I upload to our i5, then run an SQL script to load the data
into the price master.
I'm getting stuck with trying to figure out how to create the Proc
and have it use a variable name for the input price sheet and
library.
Is there a way to do this?

Here's the SQL that was generated:

CREATE PROCEDURE CCSDTATEST.INS_DOPPRCM_LOAD_FILE (
IN PRCSHT CHAR(10) ,
IN BEGDTEYMD CHAR(6) ,
IN PRCSHTLIB CHAR(10) )
LANGUAGE SQL
<<SNIP>>
INSERT INTO CCSDTATEST . DOPPRCM
( KEY1SRC , KEY1 , KEY2SRC , DTELSTUPD , REVDTE , BEGDTE , ENDDTE ,
PRCACT , QTYACT , STDPKGFLG , PRCBY , SETPRC , METHOD , KEY2 , PRCOVR1 )
SELECT 'I' , ''' || PRCSHT || ''' , 'A' ,

SELECT 'I' , PRCSHT , 'A' ,

( SELECT CURMDY FROM IQDATE . CURDATE ) ,
( SELECT CURMDY + 1 FROM IQDATE . CURDATE ) ,
BEGDTEYMD , '123149' ,
'P' , 'N' , 'N' , 'V' , 'N' , 'N' ,
ITEMID , PRICE FROM PRCSHTLIB . PRCSHT ;

COMMENT ON SPECIFIC PROCEDURE CCSDTATEST.INS_DOPPRCM_LOAD_FILE
IS 'Insert COPS Price Sheet data from Load File' ;

An example call and resulting error.

CALL CCSDTATEST.INS_DOPPRCM_LOAD_FILE('DCV1102','110217','PRICES11')

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] PRCSHT in PRCSHTLIB type *FILE not found.<<SNIP>>

I would use OVRDBF the literal PRCSHT [or any literal named on the FROM clause] to the variable named library/file on the TOFILE() parameter, along with appropriate override scope. Issue an SQL CALL QCMDEXC with a variable as the constructed command string, passed as the first parameter and the trimmed length as the second parameter [DECIMAL(15,5)]. That CALL would be used to effect the override before the SQL INSERT is performed. Another option [though not preferable IMO due to its creating an object] is to CREATE ALIAS to the variable named file, and use the ALIAS name instead of the file name in the FROM statement; probably simpler to perform that statement versus building a command string and length for the OVRDBF command string however.

Otherwise PREPARE and execute a dynamically constructed SQL statement with the names concatenated into the FROM clause.

Regards, Chuck