SAP B1 SQL - Exclude Cancelled Documents

[tinplate]

Hi, I have written the below simple SQL for reporting on Sales Orders, where the warehouse location is specific.

My question is, how do I make the report exclude all cancelled documents from the list? I have put a CASE statement in the SQL that shows me that there are some cancelled documents, but I'd like the report to run and automatically not show any cancelled documents:

Thanks in advance 🙂

SELECT

T0."DocNum"

, T0."CardName"

, T0."NumAtCard"

, CASE

WHEN T0.CANCELED = 'Y' then 'Cancelled'

WHEN T0."DocStatus" = 'O' then 'Open'

WHEN T0."DocStatus" = 'C' then 'Closed'

END

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T1."WhsCode" = 'W'

AND T0."DocDueDate" >=[%0]

AND T0."DocDueDate"<=[%1]

[jitin_chawla · Accepted solution]

Hi,

Check below:

SELECT
T0."DocNum"
, T0."CardName"
, T0."NumAtCard"
, CASE
WHEN T0."DocStatus" = 'O' then 'Open'
WHEN T0."DocStatus" = 'C' then 'Closed'
END AS "Status"
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T1."WhsCode" = 'W' and T0."CANCELED" ='N'
AND T0."DocDueDate" >=[%0]
AND T0."DocDueDate"<=[%1]

Kr,

Jitin

[tinplate · Accepted solution]

Thanks Jitin, this is perfect 🙂