smahaj2
asked on
How do I pass a comma separated value from another table in an IN clause of a query in oracle.
I have two tables - table BATCH_CONTROL which has an Id and parameter like follows -
ID prog_name prog_value
1 EFT A1,A2,A3,A4
I have another table EFT_PROTESTS like this -
ID Job_Name ...... REASON_CD
01 batch1.........A1
02 batch2.........A2
03 batch3.........A5
I need to select all those rows from EFT_PROTESTS table where REASON_CD is present in BATCH_CONTROL table for Prog_name = 'EFT'
I tried to use this but does not work -
SELECT * from EFT_PROTESTS eftprot where eftprot.reason_cd IN
(select b.prog_value from BATCH_CONTROL b where b.prog_name = 'EFTPROT')
I am using ORACLE 10g.
Thanks..
ID prog_name prog_value
1 EFT A1,A2,A3,A4
I have another table EFT_PROTESTS like this -
ID Job_Name ...... REASON_CD
01 batch1.........A1
02 batch2.........A2
03 batch3.........A5
I need to select all those rows from EFT_PROTESTS table where REASON_CD is present in BATCH_CONTROL table for Prog_name = 'EFT'
I tried to use this but does not work -
SELECT * from EFT_PROTESTS eftprot where eftprot.reason_cd IN
(select b.prog_value from BATCH_CONTROL b where b.prog_name = 'EFTPROT')
I am using ORACLE 10g.
Thanks..
ASKER
Thank you for your prompt reply. I tried to LPAD it but the query does not return any results. It still shows no rows returned. it should have returned two rows as reason codes A1 and A2 are in the comma separated list in batch_control table.
ID Job_Name ...... REASON_CD
01 batch1.........A1
02 batch2.........A2
ID Job_Name ...... REASON_CD
01 batch1.........A1
02 batch2.........A2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much Jinesh. That was of great help!!
INSTR(bc.prog_value, ep.reason_cd) != 0
will work provided none of your reason_cd values are substrings of each other
and none of your reason_cd values have the prog_value delimiter in them.
To be safe, you must check the exact value of each delimited element from batch_control.
The only way I know to do that safely is with a function that splits the delimited string....
Here's an example....
CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
RETURN vcarray PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END str2tbl;
/
select * from eft_protests,
where reason_code in (select column_value from batch_control, table(str2tbl(prog_value,' ,'))
will work provided none of your reason_cd values are substrings of each other
and none of your reason_cd values have the prog_value delimiter in them.
To be safe, you must check the exact value of each delimited element from batch_control.
The only way I know to do that safely is with a function that splits the delimited string....
Here's an example....
CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
RETURN vcarray PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END str2tbl;
/
select * from eft_protests,
where reason_code in (select column_value from batch_control, table(str2tbl(prog_value,'
sorry, I see was too late to help.
Hopefully your data is populated in such a way that the INSTR way won't give false positives.
Hopefully your data is populated in such a way that the INSTR way won't give false positives.
SELECT * from EFT_PROTESTS eftprot where LPAD(eftprot.reason_cd, 2, '0') IN
(select b.prog_value from BATCH_CONTROL b where b.prog_name = 'EFT')