Return a value for no records with case statement SQL-CodePudding

I have the following query to use in SAP B1, if the customer has journal entries then it works no problem, however if they dont have any existing journal entries there is no result. I am trying to get it to display 'Unpaid' even if there are no records.

I tried counting the number of records for the customer but as soon as i group by the other fields the query no longer works. The below sample is used on one specific document that i know will return a null value.

SELECT CASE
WHEN  T0.Debit = 5921.41 AND T0.RefDate >= [%0] THEN 'Paid'
ELSE 'Unpaid'
END FROM JDT1 T0 WHERE T0.[ContraAct] = 'C004166'

CodePudding user response：

You get either one result row or none depending on whether the contract exists. But you want one row in any case. You get exactly one row guaranteed by aggregating the data. So, simply change T0.Debit to MIN(T0.Debit) or MAX(T0.Debit) and do the same with T0.RefDate and you are done:

SELECT
  CASE WHEN MAX(T0.Debit) = 5921.41 AND MAX(T0.RefDate) >= [%0] 
    THEN 'Paid' 
    ELSE 'Unpaid'
  END AS status
FROM JDT1 T0
WHERE T0.[ContraAct] = 'C004166';