DSXchange

Hi,
I have a simple job that goes from an ODBC stage to a transformer then a flat file.

I have a user defined SQL:
select #$mf_file_pfx1# || REQUEST.F_GET_MF_FILE_EXT ( CHAR(DATE(Current date),ISO), '' ) ||'~' || #$mf_file_pfx2# || REQUEST.F_GET_MF_FILE_EXT ( CHAR(DATE(Current date),ISO), '' ) as mf_Date_Qual from sysibm.sysdummy1

The two parameters are administrative variables defined as follows:
mf_file_pfx1 'BX5216.PPRP.DSOUT.VAL035.'
mf_file_pfx2 'BX5216.PPRP.DSOUT.VAL0001A.'

When I do a view data in the ODBC stage I get the proper results:
BX5216.PPRP.DSOUT.VAL035.D1045F~BX5216.PPRP.DSOUT.VAL0001A.D1045F

I compile the job and run but get the following fatal error when running:
main_program: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "|| REQUEST" was found following "6.PPRP.DSOUT.VAL035.". Expected tokens may include: "<identifier>". SQLSTATE=42601

Any ideas what is wrong?
Thanks - - John

Just wanted to add that replacing the origina sql with:
select 'BX5216.PPRP.DSOUT.VAL035.' || REQUEST.F_GET_MF_FILE_EXT ( CHAR(DATE(Current date),ISO), '' ) ||'~' || 'BX5216.PPRP.DSOUT.VAL0001A.' || REQUEST.F_GET_MF_FILE_EXT ( CHAR(DATE(Current date),ISO), '' ) as mf_Date_Qual from sysibm.sysdummy1 (No parameters)
works fine with view data and when executing the job.
- - John

So it looks like an issue with parameter value passing. Take a look at the osh generated by View Data and compare that with the generated osh (and the score) used at run time.