Query to Select Appropriate Date Field

[Former Member]

Hello,

I am trying to come up with a FMS that selects from three available date fields(Expire Date) based up the Sales Order BP CODE

Sales Order BP Code (Header ORDR.CardCode)      UDF Date field 1(Line RDR1.U_Date1)       UDF Date field 2(Line RDR1.U_Date2)      

UDF Date field 3(Line RDR1.U_Date3

If Sales Order BP Code = ABCD then select UDF Date field 1(Line RDR1.U_Date1)

If Sales Order BP Code = EFGH then select  UDF Date field 2(Line RDR1.U_Date2) 

If Sales Order BP Code = IJKL then select  UDF Date field 3(Line RDR1.U_Date3

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

Select

Case

when  $[ORDR.Cardcode]= 'ABCD' then $[RDR1.U_Date1]

when  $[ORDR.Cardcode]= 'EFGH' then $[RDR1.U_Date2]

when  $[ORDR.Cardcode]= 'IJKL' then $[RDR1.U_Date3]

End as [Date]

From ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

Thanks

[Former Member · Accepted solution]

Thanks Nagarajan!!

Works perfectly.

[Former Member]

Hi Daniel

Try CASE:

SELECT CASE WHEN OCRD.CardCode = 'ABCD' THEN RDR1.U_Date1

WHEN OCRD.CardCode = 'EFGH' THEN RDR1.U_Date2

WHEN OCRD.CardCode = 'IJKL' THEN RDR1.U_Date3

ELSE '' END AS [My Date]

FROM ........

Kind regards

Peter Juby