Re: Suggested ways to use an exceptions table

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Fabulous.  And that should work for any combination of NULL values.  
Hopefully that's good.  :)

On 7/18/2019 9:51 AM, Rob Berendt wrote:
> Worked flawlessly.  Only got the two expected rows.
>
> INSERT INTO Elevated_Authority_Exceptions ("CURRENT_USER", "JOB_NAME", "JOB_USER", "REMOTE_ADDRESS")
>      VALUES ('ROB', 'ROBS1', 'ROB', '10.10.10.10');
> INSERT INTO Elevated_Authority_Exceptions ("CURRENT_USER", "JOB_NAME")
>      VALUES ('ROB', 'ROBBATCH');
> INSERT INTO Elevated_Authority_Exceptions ("CURRENT_USER")
>      VALUES ('JIMMY');
> Select * from elevated_authority_exceptions;
> Insert into testtable ("CURRENT_USER", "JOB_NAME", "JOB_USER", "REMOTE_ADDRESS")
> values ('PORKBELLY', 'PBS1', 'PORKBELLY', '15.1.1.1');
> Insert into testtable ("CURRENT_USER", "JOB_NAME", "JOB_USER", "REMOTE_ADDRESS")
> values ('ROB', 'ROBS1', 'ROB', '10.10.10.10');
> Insert into testtable ("CURRENT_USER", "JOB_NAME", "JOB_USER", "REMOTE_ADDRESS")
> values ('ROB', 'ROBS2', 'ROB', '10.10.10.10');
> Insert into testtable ("CURRENT_USER", "JOB_NAME", "JOB_USER")
> values ('ROB', 'ROBBATCH', 'ROB');
> Insert into testtable ("CURRENT_USER", "JOB_NAME", "JOB_USER", "REMOTE_ADDRESS")
> values ('JIMMY', 'JIMMYS1', 'JIMMY', '12.1.1.1');
>
> Select *
> from testtable tt
> WHERE (tt."CURRENT_USER", tt."JOB_NAME", tt."JOB_USER", tt."REMOTE_ADDRESS") NOT IN
>     (SELECT E."CURRENT_USER", IFNULL(E."JOB_NAME", tt."JOB_NAME"),
>       IFNULL(E."JOB_USER", tt."JOB_USER"), IFNULL(E."REMOTE_ADDRESS", tt."REMOTE_ADDRESS") FROM elevated_authority_exceptions e)
> ;
>
> Rob Berendt