Select records with empty data and display the existing records

[neilos]

Hi community,

I have this SQL that is generated from CR (edited to test in SAP), which is omitting lines that have empty BP catalogue numbers and I don't want it to.
How can I get it to return the rows including the ones with empty values?

SELECT
T0.DocNum,
T0.TaxDate,
T0.NumAtCard,
T3.PymntGroup,
T1.U_Boxes,
T1.LineNum,
T1.ItemCode,
T1.Dscription,
T1.Quantity,
T1.unitMsr,
T1.PriceBefDi as [UnitPrice],
T1.LineTotal,
T0.DocTotal,
T0.VatSum,
T0.VatPercent,
T4.PrjName,
T1.U_Site,
T0.U_UR_Code,
T5.Substitute

FROM
ORDR T0
inner join RDR1 T1 on T0.DocEntry = T1.DocEntry
inner join OCRD T2 on T0.CardCode = T2.CardCode
left outer join OCTG T3 on T2.GroupNum = T3.GroupNum
inner join OPRJ T4 on T0.Project=T4.PrjCode
left join OSCN T5 on T1.ItemCode = T5.ItemCode

WHERE
T0.DocEntry = [%1] AND T5.CardCode = [%0]

[former_member11696]

Search for if isnull and you'll find how to.

CR stops processing the SQL if a null value is returned so you need to specifically test for nulls in your record selection formula.

[neilos]

Hi Don,

Thanks for the swift response.
I have flirted with 'If isNull Then Else' and 'If Not isNull ...' from within the 'Select Expert -- Record' and also in the 'Formula Editor' which was returning 'Not a boolean' error each attempt.

Am I trying this in the incorrect place or doing it wrong?

E.G

If Not isNull({OSCN.Substitute}) Then
            ({OSCN.Substitute})
    Else ({Command.ItemCode})