I am trying to make a simple SQL query in PostgreSQL to use in our team:
with findData as (
select '(''XXX'',''YYYY'')' as dataList
)
select * from findData
That gives the result:
('XXX','YYYY')
Exactly what I wanted !
But I want to use it like this in a simple SQL script:
select t.variabel1,
t.variabel2
from myTable t
where t.variabel3 in (select dataList from findData);
But this doesn't work ...
Has my problem a solution?
The datalist is treated as a single string value, currently. So, to use the 'IN', you need them seperate. Can be done like this.
with findData as (
select unnest(array['XXX', 'YYYY']) as dataList
)
select t.variabel1, t.variabel2
from myTable t
where t.variabel3 in (select dataList from findData);
The unnest function is used to expand the array into rows.