I tried to run this query in PostgreSQL 10:
select e.errordescription,
CASE
WHEN e.reworkempid is not null THEN get_empname(e.reworkempid)
else null
end
from error_log_gs e
where e.qcworkpackageid=3012175 and e.logno=1
Got the error:
set-returning functions are not allowed in CASE
Use a lateral join instead:
select e.errordescription, ge.name
from error_log_gs e left join lateral
get_empname(e.reworkempid) ge(name)
on e.reworkempid is not null
where e.qcworkpackageid = 3012175 and e.logno = 1 ;
The exact error I was facing was, 0A000: set-returning functions are not allowed in CASE.
While investigating, found that the syntax works in Postgres version 9.6 but not in 11.
To overcome the problem, I had a work around of adding one more CTE (Common Table Expression) as below which solved my problem.
cte as
(
select string_to_array("StartDate", ',') as "S1_StartDate",
string_to_array("EndDate", ',') as "S1_EndDate",
case when "FlatDisc" is null then '{0}' when "FlatDisc" ='' then '{0}' else string_to_array("FlatDisc", ',') end as "S1_FlatDisc"
from TABLE_XYZ
)
,cte2 as
(
select unnest("S1_StartDate") as "S_StartDate",
unnest("S1_EndDate") as "S_EndDate",
unnest("S1_FlatDisc") as "S_FlatDisc"
from cte
)
select * from cte2
