Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Edit: 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   |
share|improve this question
    
Shot in the dark: Does making USERNAME lowercase to match username affect the results? –  Willem Renzema Aug 27 '13 at 9:37
    
@WillemRenzema Case sensitivity doesn't matter –  Phil Aug 27 '13 at 10:41
    
Why don't you just outer join your table with dba_users and use NVL() and DECODE() to format the output? Much better way of doing it –  Phil Aug 27 '13 at 10:43
    
The short answer is that the scalar queries in the SELECT clause don't get evaluated until AFTER the WHERE clause has been evaluated. –  Adam Musch Aug 29 '13 at 1:06

2 Answers 2

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.

share|improve this answer
    
You could use a FULL JOIN or a UNION of the 2 queries. –  ypercube Aug 27 '13 at 12:26
    
@ypercube: Union would seem to give duplicate rows (I might have missed something on that, though) but the full join indeed provided a way to combine them. I updated the answer with an example of that. –  zagrimsan Aug 27 '13 at 13:26
    
@zagrimsan: Your approach is really nice, but I can't see on which instance the user isn't registered yet. The second thing is, that I can't filter the output according to the status. In the later process I need to UNION the output of the other instances according their SYS.dba_users@DB_LINK. –  Chris.V Aug 27 '13 at 14:09
    
@Chris.V: is there a table containing all the instances or is that just something that needs to be inferred (SELECT DISTINCT db_instance FROM usr_t_user_reg)? –  zagrimsan Aug 27 '13 at 14:35
    
There is no table containing the instances, but in usr_t_user_reg is composed primary key of username and db_instance. I would do a UNION of the statement and instead of using SYS.dba_users I would use SYS.dba_users@DB_LINK_X in the statements to refer the right db_instance to the corresponding SYS.dba_users@DB_LINK_X. –  Chris.V Aug 27 '13 at 14:57
  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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.