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.
The following code works.
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!
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.
Code:
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.
Code:
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!