I am saving table ids as foreign key into another table using Oracle Apex Shuttle field like(3:4:5). Now I want to use these IDS in sql query using IN Clause. I have replaced : with , using replace function but it shows
no data found
message.
The following query works fine when I use static values.
select * from table where day_id IN(3,4,5)
But when I try to use
select * from table where id IN(Select id from table2)
it shows no data found.
From what i understand you have a list like 1:2:3:4 that you want to use in a IN clause; you can transform the list into separated values like this:
select regexp_substr('1:2:3:4','[^:]+', 1, level) as list from dual
connect by regexp_substr('1:2:3:4', '[^:]+', 1, level) is not null;
This will return:
List
1
2
3
4
Then you can simply add it to your query like this:
SELECT *
FROM TABLE
WHERE day_id IN
(SELECT regexp_substr('1:2:3:4','[^:]+', 1, level) AS list
FROM dual
CONNECT BY regexp_substr('1:2:3:4', '[^:]+', 1, level) IS NOT NULL
);
Can you try below statement. It has working as you expected.
create table table1 (id number, name varchar2(20));
alter table table1 add constraints pri_cons primary key(id);
create table table2 (id number, name varchar2(20));
alter table table2 add constraints ref_cons FOREIGN KEY(id) REFERENCES table1 (id);
begin
insert into table1 values (1,'Bala');
insert into table1 values (2,'Sathish');
insert into table1 values (3,'Subbu');
insert into table2 values (1,'Nalini');
insert into table2 values (2,'Sangeetha');
insert into table2 values (3,'Rubini');
end;
/
select * from table1 where id IN (Select id from table2);
day_idthen at the second query, you're usingid.if its just a typo make sure the data you are expecting are in table2