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 |