Null Fields in SAP B1 Querry

[shahmed]

SELECT

T0.[DocDate],

T0.[CardCode] as 'Customer Code',

T0.[DocEntry] as 'DN # ',

T0.[CardName],

T0.[NumAtCard],

T0.[TrackNo],

T1.[TrnspName] as 'Freight Term',

Case when T1.[TrnspName] = 'UPS Charge' then Ceiling(Ceiling(T0.[U_XX_FreightChrg])+3) when T1.[TrnspName] is not null then Ceiling(Ceiling(T0.[U_XX_FreightChrg])+3)*0 else T0.[U_XX_FreightChrg]*0 end as 'Freight To be Charged (CAD$)'

FROM

ODLN T0 INNER JOIN OSHP T1 ON T0.[TrnspCode] = T1.[TrnspCode]

WHERE

T0.[DocStatus] = 'O'

Order by T0.[CardName]

-----------------------------------------------------------------------------------------------------

My query doesn't show line item when T1.[TrnspName] is Null. Can you help me what is wrong with it.

[former_member185682 · Accepted solution]

Hi Shahzad,

In this case you you should use LEFT JOIN instead INNER JOIN.

ODLN T0 LEFT JOIN OSHP T1 ON T0.[TrnspCode] = T1.[TrnspCode]

Hope it helps.

Kind Regards,

Diego Lother

[shahmed]

Excellent. 🙂