JDBC Query : In clause

[Mathieu Delporte]

Hello,

 

I would like to know if someone as ever used an "in clause" containning at least two values in a JDBC Query activity (but the real number of value is dynamic).

I know work around exists (like using a SQL Direct acitivy after).

But I may have miss something. Maybe one of the datatype of the input parameter allow this

 

Best regards,

Mathieu

[Manoj Chaurasia]

Well,

 

Here is a small example.

 

SELECT tst.TEST_ID

FROM APP_TEST tst

WHERE tst.TEST_CODE in ('Code1', 'Code2', Code3')

 

The number of parameters can change.

WHERE tst.TEST_CODE in ('Code1', 'Code2')

or WHERE tst.TEST_CODE in ('Code1', 'Code2', 'Code3', 'Code4')

etc...

 

It's easy to do this with a Direct SQL Activity but I would like to know if it's possible to achieve this with a JDBC Query Activity.

 

The query would be like this :

SELECT tst.TEST_ID

FROM APP_TEST tst

WHERE tst.TEST_CODE in ()

 

Best regards,

Mathieu

[Manoj Chaurasia]

No this is not possible with JDBC Query palette.

This palette supports upto 1 dynamic input for IN caluse.

 

So if you have more than 1 value to pass in IN clause, then use SQL direct palette.

[Manoj Chaurasia]

Thx,

 

I was expecting this answer.

Hope one day tibco will add this functionnality.

 

Best regards,

Mathieu

[Manoj Chaurasia]

Hi Matieu,

 

you can do this with JDBC Query activity

 

try the below query .....

 

SELECT tst.TEST_ID

FROM APP_TEST tst

WHERE tst.TEST_CODE in ('select code from Table_A)

 

Thank

Tirumal

[Manoj Chaurasia]

Sure it will works.

But this means to update the code list in the database before hand.

 

It answer my question but I think I was not clear enough when I explain my case.

I would like to do this with direct input of the activity and not with a sub-request (which seems to be impossible for the moment).

 

Thx

Mathieu

[Manoj Chaurasia]

Could you please post a sample query you wish to execute

 

Regards ~ Yeshodhan Kulkarni

[ranjith konti]

Worked for me with Oracle DB max of 1000 values.

SELECT * from EMPLOYEE where EMP_NUMBER IN ( (select regexp_substr(,'[^,]+', 1, level) EMP_NUMBER from dual connect by regexp_substr(, '[^,]+', 1, level) is not null ) )

you need to pass exact same inputs for question marks ( example: 201,202,203,204,205,206,207,208,209,210,211,212,213,214,215). You need to pass concat sequence format with comma seperated values.

 

Also something similar in netezza below , beacuse i needed the solution in netezza.

SELECT * from EMPLOYEE where EMP_NUMBER IN

 

(SELECT REGEXP_EXTRACT(a.longstring,'[^,]+',1,cast(c.IDX as int)) AS GET_VALUE_EVERY_ELEMENT

 

FROM (

 

select as longstring

 

) a

 

cross join (SELECT IDX

 

FROM _V_VECTOR_IDX

 

WHERE IDX > 0 AND IDX