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.
