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 not retrieving results for SQL IN statement, can someone help!


Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 4 years later...
  • 3 years later...

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

Link to comment
Share on other sites

  • 1 year later...

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)

Link to comment
Share on other sites

×
×
  • Create New...