Currently I attempt to generate some reports using SQL Query from SAP table. I've created a UDF (User Define Field) option on the SAP itself with option Delivered, Canceled. If users didn't fill in this UDF section, the database will display the datas NULL
I wrote the following query, however when I try to run them, its still display results with all the options available despite I only want show UDF that contain NULL values
SELECT DISTINCT CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Date ) ELSE (SELECT T5.U_Dlvr_Date) END AS 'Delivery Date',T1.RefDate AS 'Posting Date', --T1.Memo AS 'Description',
CASE WHEN T1.TransType = '18' THEN (SELECT T4.ItemCode) ELSE (SELECT T6.ItemCode) END AS 'Item Code',
CASE WHEN T1.TransType = '18' THEN (SELECT T4.Dscription) ELSE (SELECT T6.Dscription) END AS 'Item Name',
CASE WHEN T1.TransType = '18' THEN (SELECT T4.Quantity) ELSE (SELECT T6.Quantity) END AS 'Quantity',
CASE WHEN T1.TransType = '18' THEN (SELECT CASE WHEN T4.UomCode = 'Manual' THEN 'KG' END AS 'UoM') ELSE (SELECT T6.UomCode) END AS 'UoM',
--CASE WHEN T1.TransType = '18' THEN (SELECT T4.LineTotal) ELSE (SELECT T6.LineTotal) END AS 'Price',
CASE T1.TransType WHEN 18 THEN 'A/P Invoice' WHEN 30 THEN 'Journal Entry' WHEN 46 THEN 'Outgoing Payments' WHEN 60 THEN 'Goods Issue' END AS 'Document Source', --T1.BaseRef AS 'Document Source Number',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.CardName) ELSE (SELECT T5.CardName) END AS 'Supplier Name' ,
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Itm_Src) ELSE (SELECT T5.U_Itm_Src) END AS 'Source',
--CASE WHEN T1.TransType = '18' THEN (SELECT CASE WHEN T3.U_Ref_No IS NULL THEN (SELECT T3.DocNum) ELSE (SELECT CAST (T3.U_Ref_No AS VARCHAR (1200))) END AS 'Reference Number') ELSE (SELECT CASE WHEN T5.U_Ref_No IS NULL THEN (SELECT T5.DocNum) ELSE (SELECT CAST (T5.U_Ref_No AS varchar (1200))) END AS 'Reference Number') END AS 'Reference Number',
CASE WHEN T1.TransType = '18' THEN (SELECT T1.BaseRef) ELSE (SELECT T5.U_Ref_No) END AS 'Reference Number',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Itm_Orgn) ELSE (SELECT T5.U_Itm_Orgn) END AS 'Origins',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Frm) ELSE (SELECT T5.U_Dlvr_Frm) END AS 'Delivery From',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Dest) ELSE (SELECT T5.U_Dlvr_Dest) END AS 'Destination',
CASE WHEN T1.TransType = '18' THEN (Select T3.U_Dlvr_Courier) ELSE (SELECT T5.U_Dlvr_Courier) END AS 'Courier',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_awb_No) ELSE (SELECT T5.U_Awb_No)END AS 'AwB No',
--Case WHEN T7.BatchNum IS NULL THEN (SELECT T3.U_Btch_Stck) WHEN T3.U_Btch_Stck IS NULL THEN (SELECT T5.U_Btch_Stck) ELSE (SELECT T7.BatchNum) END AS 'Batch Number' ,
CASE WHEN T1.TransType = '18' THEN (SELECT CAST (T3.U_QC_Rslt AS varchar (1200))) ELSE (SELECT CAST (T3.U_QC_Rslt AS varchar (1200))) END AS 'Test Result',
CASE WHEN T1.TransType = '18' THEN (SELECT CAST (T3.U_Dlvr_Prp AS varchar (1200))) ELSE (SELECT CAST (T5.U_Dlvr_Prp AS varchar (1200))) END AS 'Purpose',
T1.BaseRef AS 'Document Reference Number',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Doc_Status) ELSE (SELECT T5.U_Doc_Status)END AS 'Document Status'
FROM OJDT T1
LEFT JOIN JDT1 T2 ON T1.TransID = T2.TransId
LEFT JOIN OPCH T3 ON T1.BaseRef = T3.DocNum
LEFT JOIN PCH1 T4 ON T3.DocEntry = T4.DocEntry
LEFT JOIN OIGE T5 ON T1.BaseRef = T5.DocNum
LEFT JOIN IGE1 T6 ON T6.DocEntry = T5.DocEntry
LEFT JOIN IBT1 T7 ON T6.ObjType = T7.BaseType and T5.DocEntry = T7.BaseEntry and T6.LineNum = T7.BaseLinNum and T6.ItemCode = T7.ItemCode
LEFT JOIN OPOR T8 ON T7.BatchNum = T8.DocNum
WHERE T2.Account = '610218' AND T1.RefDate BETWEEN '2023-02-01' AND '2023-12-31' AND T3.U_Doc_Status + T5.U_Doc_Status IS NULL
ORDER BY T1.RefDate
The UDF section is called U_Doc_Status Sample Documents Generate
You can see the images below, when I run the Query, despite I put IS NULL, the query still extract UDF that has value in it
Appreciate your help Thank You
Regards Gerald
The condition:
T3.U_Doc_Status + T5.U_Doc_Status IS NULL
Becomes true (1) if either of the columns is not null.
Since your Document Status is defined as:
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Doc_Status) ELSE (SELECT T5.U_Doc_Status) AS 'Document Status'
You will get non-null values. To get only the NULL values you should use:
T3.U_Doc_Status IS NULL AND T5.U_Doc_Status IS NULL
To clean up the code, instead of using CASE you can use IF:
IF (T1.TransType = '18', T3.U_Doc_Status, T5.U_Doc_Status) AS 'Document Status'