CASE Statement - 1st case is being ignored

Question

I have a table (usr_t_user_reg) in which I register all the users of the database. Now I want to create a view (usr_v_user_not_reg) which shows me all the users that I haven't registered yet or users that are no longer on the database.

The table looks like this:

| username | db_instance | forename | surname | ...... |
|----------|-------------|----------|---------|--------|
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB2      |   xxxxx  |   xxxx  | ...... |

Here is the statement to create the view:

CREATE OR REPLACE FORCE VIEW usr_v_user_not_reg AS 
 SELECT username "User", db_instance "Instance",
 (
  CASE 
    WHEN username IN
    (
      SELECT username 
      FROM SYS.dba_users
      MINUS
      SELECT username 
      FROM usr_t_user_reg
    ) THEN 'not registered'
    WHEN username IN
    (
      SELECT username 
      FROM usr_t_user_reg
      MINUS
      SELECT username 
      FROM SYS.dba_users
    ) THEN 'no longer present'
  END
 ) "Status"
 FROM usr_t_user_reg
 WHERE db_instance = 'DB1'
 AND username NOT IN
 (
  SELECT username
  FROM usr_t_user_reg
  INTERSECT
  SELECT username
  FROM SYS.dba_users
 );

At first I thought it would work fine and then I noticed that the first case is kind of being ignored. I have some users who are not registered yet and they are not displayed in the output. If I use the first case as a standalone query

SELECT username 
FROM SYS.dba_users
MINUS
SELECT username 
FROM usr_t_user_reg
WHERE db_instance = 'DB1';

then it works. As soon as I want to use it in the whole query (even without the CREATE command) it doesn't work. The second case works without any problems.

I think I'm missing something, but I don't know what.

Additional considerations

I think I forgot to mention something. On the database are multiple instances and with the view I want to know on which instance there is a user who isn't registered yet or who isn't available anymore. The view I want to create should have an output similiar to this:

| username | instance |     status     |
|----------|----------|----------------|
|   XYA    |   DB1    | not registered |
|   XYB    |   DB2    | not registered |
|   XYC    |   DB3    | non-existend   |

Answer 1

To me it looks much like you're asking only the registered users, as the SELECT acting as the basis for the view is:

SELECT 
[...]
 FROM usr_t_user_reg

Thus having "some users who are not registered yet and they are not displayed in the output" is what should be expected.

Having something like this would show the not (yet) registered users:

  SELECT
    u.username "User", 
    r.db_instance "Instance",
    NVL(r.db_instance,'not registered yet') "Status"
  FROM 
    SYS.dba_users u LEFT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

That will, however, not give those users that are still in the "registered" table but are not anymore DB users, for that you could use the parallel:

  SELECT
    r.username "User", 
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
  FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

The SQL above is probably possible to combine nicely, and is likely to be sub-optimal, but it did work on quick testing...

Edit: Full outer join version combining the two queries above:

  SELECT
    NVL(r.username, u.username) "User", 
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 
      CASE NVL(r.db_instance,'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END
    END "Status"
  FROM SYS.dba_users u FULL OUTER JOIN usr_t_user_reg r ON u.username = r.username;

Edit 2: A version providing also the name of the instance(s) the user is not registered, the name of the instance(s) the non-existent user is registered to, and filtering out all of the cases where status is OK:

SELECT * 
FROM (
  WITH t1 AS (
    SELECT 
      u.username, 
      i.db_instance
    FROM SYS.dba_users u, ( SELECT DISTINCT db_instance FROM usr_t_user_reg ) i
  )
  SELECT 
    t1.username "User", 
    t1.db_instance "Instance", 
    CASE NVL(r.db_instance, 'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END "Status"
  FROM t1 LEFT OUTER JOIN usr_t_user_reg r ON t1.username = r.username AND t1.db_instance = NVL(r.db_instance, t1.db_instance)
  union
  SELECT
    r.username "User", 
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
  FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username
)
WHERE "Status"!='OK';

It starts getting uglier and even less optimal, but again that did work on the test setup.

Answer 2

1. There are a lot of strange things with your query your main select selects usernames that are not in sys.dba users (you exclude them with yout NOT IN clause, on the other hand your first case is concerned with users that are from SYS.dba_users. Therefore the clause will never be chosen. This is why you think it will be ignored.

2. sometimes you are referencing the users from usr_t_user_reg where db_instance='DB1' sometimes you are referncing all users from usr_t_user_reg

3. Basic set theory: A minus (B intersect A) = A minus B. so your select in the NOT IN clause can be simplified to the equivalent NOT IN select username from sys.dba_users

4. To retrieve data distirbuted to more than one table you should try to use the join operator if possible . This is the prefered method when working with SQL. This will make your queries simpler