wrong number of parameters

dsdevper · Post by **dsdevper** » Tue Sep 02, 2008 7:31 pm

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

dsdevper · Post by **dsdevper** » Tue Sep 02, 2008 7:33 pm

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

my source and target are also DB2 stages

ray.wurlod · Post by **ray.wurlod** » Tue Sep 02, 2008 9:06 pm

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.

dsdevper · Post by **dsdevper** » Tue Sep 02, 2008 10:28 pm

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.

dsdevper · Post by **dsdevper** » Tue Sep 02, 2008 10:31 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Sep 02, 2008 11:33 pm

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.

dongyingying · Post by **dongyingying** » Mon Feb 13, 2012 1:05 am

Hi ,dsdevper

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

BI-RMA · Post by **BI-RMA** » Mon Feb 13, 2012 8:43 am

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.