Does DS support insert into XXX(XX) select from XX statement

caola · Post by **caola** » Tue Apr 22, 2014 8:56 am

My job design is like:

several db2 api to select------> funnel--------->db2 api

In the target db2 api, I use below user defined sql. The ? stands for the only one field that output by funnel.
I got an error: This SQL statement did not contain the same number of parameters as columns on this link.

My question is does DS support this kind of insert statement?

Code: Select all

INSERT INTO TargetTable  (A) 
select
     b
   from
      TableX
    where
      b  = ?
and c='XX';

chulett · Post by **chulett** » Tue Apr 22, 2014 9:43 am

Technically, yes - as long as the number of bind parameters in the SQL is equal to the number of columns defined in the stage - which is what the error is saying. Note that (at least in the past) the ODBC stage doesn't care about that little detail.

kduke · Post by **kduke** » Thu Apr 24, 2014 3:55 pm

Syntax varies on this from database to database.

caola · Post by **caola** » Fri Apr 25, 2014 5:26 am

The database is db2. I tried odbc and it works, but still don't know why db2 api gave that error, as the number of parameters(where ? shows) is the same as the number of columns...

Thanks guys. I will mark it as workaround.

chulett · Post by **chulett** » Fri Apr 25, 2014 6:53 am

Since they're not numbered (as in Oracle) you have to realize that only columns marked as Key columns get used in the 'where' clause. Guessing yours isn't.