JDBC Query not retrieving results for SQL IN statement, can someone help!

[Manoj Chaurasia]

Hi,

 

In JDBC query when I use IN statement and pass multiple variables using prepared parameter, I am not able to retrieve any records but it works fine in SQL direct.

 

For example, I am having an EMPLOYEE table which has EMP# as its primary key.

Now that I have list of EMP numbers as input, I do a select using JDBC query as below

 

SELECT * from EMPLOYEE where EMP_NUMBER IN ()

 

and add a prepared parameter - EMPNo (VARCHAR)

 

In the mapping, EMPNo ---> tib:trim(concat("'",tib:concat-sequence-format($Start/list/EmpNo, "' ,'", false()),"'")) to take the list of EMP numbers in the format as '1234','4567','7891','2345'.

 

or if I hardcode EMPNo like '1234','4567' also it doesnot work.

where as, it works only for one EMPNo like hardcoding just '1234'

 

Please can anyone help me on this

 

Thanks in advance.

 

Regards,

Chaitra

[Manoj Chaurasia]

You need as many number of bind variables as your in clause variables to make it work e.g.

 

SELECT * from EMPLOYEE where EMP_NUMBER IN '1234', '4567','7891','2345' then query should look like

 

SELECT * from EMPLOYEE where EMP_NUMBER IN (,,,)

 

-S

[Manoj Chaurasia]

Dinesh a crit:

 

I have also faced the same issue and it will work only in sqldirect.

But sorry i dont know the reason.

 

It will be helpful if any body can tell us the reason behind it.

 

Regards,

Dinesh

 

Hi,

 

Check this : http://www.tibcommunity.com/message/36144#36144

 

The reason is that the JDBC Query activity replace one "" with only one parameter. Even if you build your parameter string for looking like "several parameters", the activity will use it as a whole.

 

It would be great if TIBCO added a parameter type "IN clause" (repeating node in the input) for handling this particular case

 

regards,

mathieu

[Manoj Chaurasia]

I have also faced the same issue and it will work only in sqldirect.

But sorry i dont know the reason.

 

It will be helpful if any body can tell us the reason behind it.

 

Regards,

Dinesh

[Manoj Chaurasia]

Hi,

 

Thanks for the answer.

 

Can anyone tell me how to use ARRAY datatype, when I declare Prepared Parameter as an array in JDBC query.I saw that it becomes a complex element but not sure how to pass the values using this datatype

 

Ragards,

Chaitra

[Ran Alcobi]

Hi, You can use REGEXP to solve this issue:

 

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 ) )

 

 

the input variable should be a comma separated string like: '111,2222,3333,...'

 

Good Luck :)

[ranjith konti]

Worked for me with Oracle DB. Thanks !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.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 <= 1000

           )c

WHERE GET_VALUE_EVERY_ELEMENT IS NOT NULL)