Forum Discussion

Stalsy's avatar
2 years ago

JDBC Prepared parameter that is comma separated used in another SQL query

My problem is similar to question SQL Datasource Prepared Properties using a Property that is a comma separated list  but with one difference: 

The Prepared property comes from another JDBC SQL Datasource: I have nested JDBC Datasources.  The outer one returns the customerID and uses the DB2 listagg() function to list the customer's account_numbers, so say a customer with 3 accounts would look like this:  '0010012345',111111,22222,333333 (Customer_id is a string and account numbers are integers).

The inner DataSource uses 2  Prepared Properties customerId and account_numbers and the WHERE clause includes:

and T1.customer_id = :customerId and T1.account_Id IN (:account_numbers).  

This returns nothing. I have tried as the above question suggests to use quotes  '0010012345',111111','22222','333333 but this does not work either.

 I "solved" my issue by making the outer datasource return one account per row, but this means I then call the API multiple times using the customer ID and one account per call (so for the above example I call the API 3 times) but that means I never get to call/test the API with multiple accounts.

No RepliesBe the first to reply