Case Statement help
I have this case statement I am trying to build, but of course it get errors.... Can someone look at it and tell me where I am missing something?
CASE WHEN ((l.Transtype = 13) THEN (Select Docstatus
WHERE JDT1 INNER JOIN OINV ON JDT1.TransId = OINV.TransId))
CASE WHEN ((l.Transtype = 14) THEN (Select Docstatus
Where JDT1 INNER JOIN ORIN ON JDT1.TransId = ORIN.TransId)) WHEN 'O' Then 'Open' WHEN 'C' Then 'Closed' END END AS DocStatus.
I am trying to tell it to look at the transtype from JDT1, and based on that transtype to go to the document and get the status and return open or closed.
Any help is appreciated.....
Gordon Du replied
Case clause does not support embed function.
The best result I can reproduce is something like:
SELECT CASE T0.Transtype WHEN 13 THEN T1.Docstatus
WHEN 14 THEN T2.Docstatus END AS DocStatus1
FROM OJDT T0
LEFT JOIN OINV T1 ON T1.DocNum = T0.Ref1 AND T0.Transtype = 13
LEFT JOIN ORIN T2 ON T2.DocNum = T0.Ref1 AND T0.Transtype = 14
WHERE T0.transiD like '[%0\]%' AND T0.Transtype IN (13,14)