5

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

Eduardo Pelais
  • 1,627
  • 15
  • 21
srinu
  • 51
  • 1
  • 1
  • 3
  • Case _expression_, not statement. – jarlh Nov 30 '17 at 13:15
  • 4
    Error seems pretty clear to me, what is your question? – HoneyBadger Nov 30 '17 at 13:15
  • 1
    Actually the question is kind of self-evident, much more so than the cryptic error message. It really _isn't_ clear why CASE returns a set here when in other use-cases it seems like it is a row-by-row operation (eg. every single CASE tutorial you come across in a search). Fortunately @linoff understood that. – MikeB2019x Jan 21 '22 at 21:30

1 Answers1

8

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 ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786