Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

JDBC Query : In clause


Mathieu Delporte

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 5 years later...
  • 4 years later...

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

Link to comment
Share on other sites

×
×
  • Create New...