sqlpostgresqlcommon-table-expression

Use a string value from a CTE in an IN-clause in a simple SQL query


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?


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.