Re: Variable File names in SQL Procedure

[CRPence <CRPbottle@xxxxxxxxx>]

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