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 |
USERNAME
lowercase to matchusername
affect the results? – Willem Renzema Aug 27 '13 at 9:37NVL()
andDECODE()
to format the output? Much better way of doing it – Philᵀᴹ Aug 27 '13 at 10:43