DSXchange

Hi I am using lookup stage in which i have to use user generated sql consisting of one environmental variable and job paramater.
i am getting an error :

T_DISTRICT,0: Fatal Error: Fatal: [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'SELECT TA2008.T_DISTRICT.I_DISTRICT,TA2008.T_DISTRICT.C_STATUS_DIST,TA2008.T_DISTRICT.D_STATUS_DIST,TA2008.T_DISTRICT.C_TYPE_DIST,TA2008.T_DISTRICT.C_DIST_CHART_TYPE,TA2008.T_DISTRICT.C_STATUS_FSP,TA2008.T_DISTRICT.I_REGION,TA2008.T_DISTRICT.PEIMS_ESC,TA2008.T_DISTRICT.I_GEO_REGION,TA2008.T_DISTRICT.N_DISTRICT,TA2008.T_DISTRICT.I_COUNTY FROM TA2008.T_DISTRICT WHERE TA2008.T_DISTRICT.I_DISTRICT=? AND I_DISTRICT = '255000''. See following DB2 message for details.


query i am using is:

SELECT #$SN##year#.T_DISTRICT.I_DISTRICT,#$SN##year#.T_DISTRICT.C_STATUS_DIST,#$SN##year#.T_DISTRICT.D_STATUS_DIST,#$SN##year#.T_DISTRICT.C_TYPE_DIST,#$SN##year#.T_DISTRICT.C_DIST_CHART_TYPE,#$SN##year#.T_DISTRICT.C_STATUS_FSP,#$SN##year#.T_DISTRICT.I_REGION,#$SN##year#.T_DISTRICT.PEIMS_ESC,#$SN##year#.T_DISTRICT.I_GEO_REGION,#$SN##year#.T_DISTRICT.N_DISTRICT,#$SN##year#.T_DISTRICT.I_COUNTY FROM #$SN##year#.T_DISTRICT WHERE #$SN##year#.T_DISTRICT.I_DISTRICT=? AND I_DISTRICT = '255000';

$SN is environmental varible TA.
year is job parameter 2008.

To be strange when i am using SQL generated by coloumn list it is running correctly:

SELECT TA2008.T_DISTRICT.I_DISTRICT,TA2008.T_DISTRICT.C_STATUS_DIST,TA2008.T_DISTRICT.D_STATUS_DIST,TA2008.T_DISTRICT.C_TYPE_DIST,TA2008.T_DISTRICT.C_DIST_CHART_TYPE,TA2008.T_DISTRICT.C_STATUS_FSP,TA2008.T_DISTRICT.I_REGION,TA2008.T_DISTRICT.PEIMS_ESC,TA2008.T_DISTRICT.I_GEO_REGION,TA2008.T_DISTRICT.N_DISTRICT,TA2008.T_DISTRICT.I_COUNTY FROM TA2008.T_DISTRICT WHERE TA2008.T_DISTRICT.I_DISTRICT=? AND I_DISTRICT = '255000';

Please help.

Thanks

In the above post i am using DB2 stage for lookup.

my source and target are also DB2 stages

Look in the job log to verify that the job parameters are all being resolved correctly and, indeed, that the correct number of parameter markers (corresponding to the column names on the link) are being supplied. It is in the latter area that the fault most probably lies.

Hi Ray
Ihave changed gnerated sql query by coloumns "SELECT TA2008.T_DISTRICT.I_DISTRICT,TA2008.T_DISTRICT.C_STATUS_DIST,TA2008.T_DISTRICT.D_STATUS_DIST,TA2008.T_DISTRICT.C_TYPE_DIST,TA2008.T_DISTRICT.C_DIST_CHART_TYPE,TA2008.T_DISTRICT.C_STATUS_FSP,TA2008.T_DISTRICT.I_REGION,TA2008.T_DISTRICT.PEIMS_ESC,TA2008.T_DISTRICT.I_GEO_REGION,TA2008.T_DISTRICT.N_DISTRICT,TA2008.T_DISTRICT.I_COUNTY FROM TA2008.T_DISTRICT WHERE TA2008.T_DISTRICT.I_DISTRICT=? AND I_DISTRICT = '255000'; " to Enter custom sql query and removed TA2008.T_DISTRICT.I_DISTRICT=? from the above query in the lookup DB2 stage..

Then it worked.

But i am not sure this is the correct way or not.

Please let me know.

Thanks
Suman.

Hi Ray
Ihave changed gnerated sql query by coloumns "SELECT TA2008.T_DISTRICT.I_DISTRICT,TA2008.T_DISTRICT.C_STATUS_DIST,TA2008.T_DISTRICT.D_STATUS_DIST,TA2008.T_DISTRICT.C_TYPE_DIST,TA2008.T_DISTRICT.C_DIST_CHART_TYPE,TA2008.T_DISTRICT.C_STATUS_FSP,TA2008.T_DISTRICT.I_REGION,TA2008.T_DISTRICT.PEIMS_ESC,TA2008.T_DISTRICT.I_GEO_REGION,TA2008.T_DISTRICT.N_DISTRICT,TA2008.T_DISTRICT.I_COUNTY FROM TA2008.T_DISTRICT WHERE TA2008.T_DISTRICT.I_DISTRICT=? AND I_DISTRICT = '255000'; " to Enter custom sql query and removed TA2008.T_DISTRICT.I_DISTRICT=? from the above query in the lookup DB2 stage..

Then it worked.

But i am not sure this is the correct way or not.

Please let me know.

Thanks

At one level it's correct because it works. However you have not ascertained why what you tried earlier does not work. Knowing this would perhaps allow you to create more efficient jobs in future.

Hi ,dsdevper

Did you get the answer about issue?Today I met one .

The original SQL-Statement looks a bit strange to me because the same column in the queried table is referenced twice in the where-clause - once fully qualified and once only by column-name. Looks to me like DB2 does not like this construct. Just a guess. I did not test that.