Using 'in' selection criteria in a jdbc select adapter service

, ,
1

I have a simple sql select I’m trying to run as a jdbc adapter service - one of the parameters is a list of values for the ‘in’ part of the selection criteria - how do I format this within flow to pass the correct value in? There doesn’t seem to be a simple way of doing this?

The SQL statement I want to run has the following structure (simplified for clarity):

select count(*) from table1 where field1 = ‘VALUE’ and field2 in (‘a’,‘b’,‘c’)

Passing in the parameter for field1 is straightforward enough but how do I pass in the list of values for field2? If I pass in the list as shown (‘a’,‘b’,‘c’) then statement looks for a match based on the whole string not the individual values (so it won’t find a match for ‘a’ but will match on (‘a’,‘b’,‘c’).

1 Like
2

Hi,

Problem is very interesting.
You can try this with Custom/Dynamic adapters. But again you can’t pass three input as it will not allow you bind the query.

  1. You can slightly modify your query, instead of using IN you can use 3 OR’s
  2. if your 3 IN values are fixed then you can write that in custom sql instead of passing that at run time.

I am still checking with your problem, if I found anything I will notify you.

Please correct me if I am wrong.

Thanks,
Vicky

3

Try building the IN string at run time using Dynamic sql.

assuming you have the list of IN values for field2 in a string list say field2stringlist.
Call pub.string:makeString with elementList=field2stringlist and separator=‘,’ and map the output to field2InVlaues
select count(*) from table1 where field1 = ‘VALUE’ and field2 in (‘${field2InVlaues}’)

HTH

2 Likes
4

Hi Hugh,

IN, LIKE are not supported in Select template of JDBC adapter.

As pointed out by Vicky and Suresh, you need to use Dynamic SQL.

Thanks,
Rankesh

5

Thanks for the replies - I have tried creating a dynamic SQL adapter service and that doesn’t look like it works either, to test the theory I created the service with a single input parameter that is the entire sql command I’m trying to run (so the SQL entered in the dynamic service is just a ? character). The input parameter is than given the value:

select count(*) from table where field1 = ‘Value’ and field2 in (‘a’, ‘b’, ‘c’)

The documentation for the Dynamic adapter suggests this should work but it doesn’t.

If I edit the sql statement in the service to:

select count(*) from table where field1 = ?

This works ok if I pass value in as the input. If I then change the sql to:

select count(*) from table where field1 = ? and field2 ?

Then pass in:
value
in (‘a’, ‘b’, ‘c’)

This doesn’t work.

If I edit the sql statement in the service to:

select count(*) from table where field1 = ? and field2 in ?

Then pass in:
value
(‘a’, ‘b’, ‘c’)

This also does not work.

If I edit the sql statement in the service to:

select count(*) from table where field1 = ? and field2 in (?)

Then pass in:
value
‘a’, ‘b’, ‘c’

This also does not work.

If I use the ‘in’ and pass a single value without the brackets and single quotes then the statement executes ok. It doesn’t look like it can handle the list of values being given to it - but I would have expected the example where I just pass in the whole sql statement to have worked. It appears that as soon as I introduce the single quote character it no longer works - is there something I need to do to escape these characters?

All ideas appreciated - I didn’t think this was a difficult task but it’s proving awkward!

1 Like
6

It doesn’t sound you are using dynamicSQL based on the format for the inputs…are you using customSQL template?

HTH,
RMG

7

Hugh,
Try a simpler test…
Create a dynamic sql adapter - select count(*) from table1 where field2 in (${field2InVlaues})

Do not use ? and instead use ${} as stated above. Once you save the adapter service it will create an input field2InVlaues. Run the service and give ‘a’ or ‘a’,‘b’ as input to field2InVlaues. It will work.

1 Like
8

Excellent - thank you, I tried that and it’s working. Thanks again for your help.

9

You are welcome :slight_smile:

10

Hi all,

I have a problem with dynamic sql.
After I created the dynamic query service then run the service it works.
But if I run the service for the second time with different value, service run with the old value.
It seems the service keep the first time value to my ${where} condition.

Are there any configuration to run this dynamic query service ?

this is my query :

select keyId from financial_Activity ${where}

these are my scenario :

  1. run service with “where” value => where TYPE in ( ‘20631’)
  2. run service with “where” value => where TYPE in (‘20631’, ‘43222’, ‘67788’)
  3. run service with “where” value => where TYPE in (‘67788’)

all of them returned the same value, which is expect different value.

Thanks.

11

Hi Junedi,

can you provide a screen shot of the invocation of your adapter service?

Which adapter version are you using on which wM version?
Any Fixes applied to these?

Regards,
Holger