Record selection only non zeros and nulls

[Former Member]

Hi,

I have 3 tables.  BK (order) left joined to BP (line item) , BP left joined to BS (detail)

There is not always a detail row for a given line item.

I want to filter out selections where there is a detail row, but it has a price (PR_PREIS_ME) of 0.  I need lines where the is no detail (NULLS).

BK.AH_IDENT   BK.ID     BP.POS_NR   BP.PP_QM_FAKT   BS.BOM_PRODUKT   BS.PR_PREIS_ME

152315        2527245   1           25.5            1000012          0

152315        2527245   1           25.5            9101             0

152315        2527245   1           25.5            9201             0

152315        2527245   1           25.5            7432             0

152315        2527245   1           25.5            9101             0.14

152315        2527245   1           25.5            9201             8.91

152315        2527245   1           25.5            7432             18.89

152315        2527245   2           55.25           1000012          0

152315        2527245   2           55.25           9101             0

152315        2527245   2           55.25           9101             0.14

152315        2527245   900         0               NULL             NULL

152315        2527245   3           0               NULL             NULL

I'm wrestling with the syntax, but syntax is winning.

[DellSC · Accepted solution]

Try this - you'll have to enter this in the formula editor in the Select Expert.

(IsNull({BS.PR_PREIS_ME}) or {BS.PR_PREIS_ME} > 0)

Note:  To get this to work you MUST use the parentheses if you have any additional filter criteria beyond this condition.

-Dell

[Former Member]

Hi,

Dell is on the right track but you indicated that you want the NULL lines included and it looks like there are 2 fields that could have this based on your sample report. The Select Expert formula would be:

((IsNull({BS.PR_PREIS_ME}) or (IsNull({BS.BOM_PRODUKT})or {BS.PR_PREIS_ME} > 0)

Heather

[Former Member]

Hi,

Thank you.  Both BS. fields are NULL because the is no row being linked in the BS. table, so they either both or neither.  They're defined as NOT NULL in the table itself.

Why would this necessarily need to be entered in Select Expert?

[DellSC]

If you're linking tables together to get the data for your report, it needs to be in the Select Expert.  If you're using a command (SQL Select statement) instead of tables, it needs to be in the join or where clause of the command.

-Dell