LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: kerry lambret

How can I write a dynamic SQL using �DB Execute�

Eg:

Select *
from
test_table
where account in ('xxxxxxxx','yyyyyyyy')

I need to feed the list of accounts into the DB Execute as a string rather than multiple lines. Can I use �in :1� rather than �= :1� in DB Execute?
Thank you

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

How many items need to go into the list? I ran into a problem with Oracle 9i that it would only accept 1024 items in the list.

You are not required to use :1, but rather you could use something like this:

query="select * from test_table where account in (" + Account_String + ")"
sqlSelect(1,query)

Then use an AGG node in front of the DbExecute to build the Account_String - WITH commas and quotes if necessary.

a="'" + Account +"'"
s=s.joinStrings(a,",")
emit s as Account_String
where lastInGroup

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Out of Interest can you not use JDBC and using JDBC Bindings?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Both our JDBC and ODBC node support binding (see node documentation). However in both cases, just because of how Oracle works with values, you can't bind values to an "in" statement. Bindings are for single values, and the "in" statement takes an array, so you will get type issues. Another way to do this, with a bit more work, is to load the values into a table and then do a join against the table. While cumbersome, it is another option that might suit your needs, depending on situation.

Cheers
Rich