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
wrong number of parameters
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 35
- Joined: Tue Feb 07, 2012 2:28 am
- Location: China BeiJing
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.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon