Skip to Content
avatar image
Former Member

Record selection only non zeros and nulls

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 06, 2015 at 09:20 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 06, 2015 at 10:56 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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