Dynamic IN Clause.

[bean1234]

Hello Experts ,

I am trying to build a dynamic in clause from a string passed in as an input parameter as follows but the following code doesnt work, but when I replace the IN clause content with a string literal it returns results, can you please let me what might be the issue here.

The following code does not work.

SELECT 
  TABLE1,
  COLUMN1,
  TABLE2,
  COLUMN2,
  SECOND_OPERAND,
  OPERATOR_VALUE
FROM 
  JOIN_CONDITIONS JC ,
  JOIN_OPERATOR_REF JOR
WHERE 
  VIEW_NAME = 'Transaction' and
  TABLE1 in ( select * from table ( CAST ( birtpack.Dynamic_In('''POSITION_DETAIL'',''SECURITY''') AS sys.dbms_debug_vc2coll ))) and
  (TABLE2 in( select * from table(CAST ( birtpack.Dynamic_In('''POSITION_DETAIL'',''SECURITY''') AS sys.dbms_debug_vc2coll )))  or TABLE2 = NULL) AND
  JC.OPERATOR_NAME = JOR.OPERATOR_NAME ;

The following code works.

SELECT 
  TABLE1,
  COLUMN1,
  TABLE2,
  COLUMN2,
  SECOND_OPERAND,
  OPERATOR_VALUE
FROM 
  JOIN_CONDITIONS JC ,
  JOIN_OPERATOR_REF JOR
WHERE 
  VIEW_NAME = 'Transaction' and
  TABLE1 in ('''POSITION_DETAIL'',''SECURITY''')  and
  (TABLE2 in( '''POSITION_DETAIL'',''SECURITY''')  or TABLE2 = NULL) AND
  JC.OPERATOR_NAME = JOR.OPERATOR_NAME ;

The function birtpack.Dynamic_In returns a table for a comma delimited string, I have tested it and it returns a table properly for with one row for each string delimited by comma.It seems to me that the in clause doesnt accept dynamically giving the parameters.Any help would be greatly appreciated.

Thanks in advance!

 

[lewisp]

I'm a little confused about your strings. Do you want to match POSITION_DETAIL or SECURITY, or 'POSITION_DETAIL','SECURITY' including the single quotes? In both of your SQL cases, you are passing in a single string that looks like this:

[tt]'POSITION_DETAIL','SECURITY'[/tt]

The IN clause of the second SQL is looking for this exact string and not the two table names independently. I think the WHERE of SQL 2 should look like this:

  TABLE1 in ('POSITION_DETAIL','SECURITY')  and
  (TABLE2 in( 'POSITION_DETAIL','SECURITY')

which would stop the SQL working if the string is held as in my first example.

I hope this is clear.

 

[bean1234]

Hello Lewis,

The second code I had a typo actually I am passing 'POSITION_DETAIL','SECURITY' in the working code, the problem is you cant have a dynamic IN clause, you will have to use Dynamic SQL , I was avoiding to not to use it , but looks like there is no way out.

 

[lewisp]

Reference cursors are quite easy to use. I would suggest using them in your birtpack.Dynamic_In procedure.