Running a SQL statement using parameter

aaikat · Post by **aaikat** » Wed Jun 21, 2006 4:20 am

Can I do the following(dynamic building of SQL query) in a server job :

I am using a DB2 UDB stage. In it output custom SQL I write :

#SQL_STATEMENT#

Then define the parameter SQL_STATEMENT as

Select count(*) as all from ABC.X_CONTACTS where XID = 'RVH'

Now when I run the job I get error like :

An unexpected token "<<missing table>>" was found following "SELECT all FROM ". Expected tokens may include: "<space>". SQLSTATE=42601

SQLExecDirect: Error executing statement 'SELECT all FROM <<missing table>>'. See following DB2 message for details.

kumar_s · Post by **kumar_s** » Wed Jun 21, 2006 4:45 am

Is "count(*) as" and the table name alone given as parameter?
If so check whether the parameter is getting passed properly.

sb_akarmarkar · Post by **sb_akarmarkar** » Wed Jun 21, 2006 5:40 am

I think 'all' is a reserve keyword you are trying to assign to count(*)...
Please try some other name like

Select count(*) as A from ABC.X_CONTACTS where XID = 'RVH'

Thanks,
Anupam

DSguru2B · Post by **DSguru2B** » Wed Jun 21, 2006 6:39 am

If it is a reserved word, use it in double quotes. You should be fine then.

chulett · Post by **chulett** » Wed Jun 21, 2006 6:57 am

OR just get rid of it, the 'as XXX' part is completely ignored.