Case Statement help

[Former Member]

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

From OINV

WHERE JDT1 INNER JOIN OINV ON JDT1.TransId = OINV.TransId))

CASE WHEN ((l.Transtype = 14) THEN (Select Docstatus

From ORIN

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.....

Thanks

[Former Member · Accepted solution]

Hi,

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)

Thanks,

Gordon

[Former Member · Accepted solution]

Once again Gordon, you have solved the issue.....

Thanks!