Re: Suggested ways to use an exceptions table

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Just spitballing, but let's it would be something like this:

WHERE (DATA.COLA, DATA.COLB, DATA.COLC, DATA.COLD) NOT IN
  (SELECT EXCP.COLA, IFNULL(EXCP.COLB, DATA.COLB),
    IFNULL(EXCP.COLC, DATA.COLC), IFNULL(EXCP.COLD, DATA.COLD) FROM EXCP)

Honestly, I don't know if this will work like you want or if it's even 
syntactically correct, but it seems like a start.  Basically you want 
matches, and you want NULL to match anything, so just substitute the 
original value when the exception column is NULL.



On 7/17/2019 2:50 PM, Rob Berendt wrote:
> Suppose you have a table with multiple columns.  Among these columns are ColA, ColB, ColC, ColD.
> And you want a flexible way to omit certain rows from this table appearing on a report based on an exceptions table.
> This exceptions table has four columns ColA, ColB, ColC, ColD
> So if a row from the table matches those columns in the exception table do not display that row.
>
> Now let's complicate it up a bit.
>
> ColA will always have a value in the exceptions table.  However some of the other columns may be null.
>
> If ColD is null then omit rows where the other three match.
> If ColD and ColC are null then omit rows where the other two match.
> If ColD, ColC and ColB are null then omit all rows where ColA matches.
> Unless it doesn't complicate items too much then let's forget about where ColD is not null but ColC is.
>
> How would you code that in a SQL Procedure?  I take it that a procedure would be necessary as it is probably too much to ask for in a basic select statement.
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>            Garrett, IN 46738
> Ship to:  Dock 108
>            6928N 400E
>            Kendallville, IN 46755
> http://www.dekko.com