Outer join in case statement

Question

I have this below query:

SELECT U.NAME , U.TYPE , U.REFERENCED_NAME , U.REFERENCED_TYPE,
CASE 
WHEN U.REFERENCED_TYPE= 'TABLE' and A.TABLE_NAME= U.REFERENCED_NAME 
  THEN 'TABLE EXISTS'
WHEN U.REFERENCED_TYPE= 'PROCEDURE' and A.TABLE_NAME= U.REFERENCED_NAME 
   THEN 'PROCEDURE EXISTS'
END
FROM  USER_DEPENDENCIES U , user_tables a  
WHERE U.name ='RANDOM_PROCEDURE_NAME' and U.REFERENCED_NAME = A.TABLE_NAME;

My problem in this query is this : U.REFERENCED_NAME = A.TABLE_NAME such join is giving ME only the specific condition.I want in table USER_DEPENDENCIES to know the tables that exists even if it doenst exists in table user_tables a. I WANT to include an outer join but I am not capable to do that. can anyone help me with that?

So you want all user_dependencies (referencing tables, procedures, triggers, ...)? And for those referencing a table or a procedure you want to know if there exists a table in user_tables with the same name as the referenced table or procedure? You are looking for procedures having the same name as tables in all_tables? Please clarify. — Thorsten Kettner, Oct 23 '13 at 11:52

Srini V · Answer 1 · 2013-10-23T11:58:25.083

1

Something like this?

SELECT
      U.NAME,
      U.TYPE,
      U.REFERENCED_NAME,
      U.REFERENCED_TYPE,
      CASE
          WHEN U.REFERENCED_TYPE = 'TABLE'
              AND A.TABLE_NAME = U.REFERENCED_NAME
          THEN
              'TABLE EXISTS'
          WHEN U.REFERENCED_TYPE = 'PROCEDURE'
              AND A.TABLE_NAME = U.REFERENCED_NAME
          THEN
              'PROCEDURE EXISTS'
          ELSE
              'NOT DEFINED'
      END
          TYPE_1
FROM
      USER_DEPENDENCIES U,
      USER_TABLES A
WHERE
      U.NAME LIKE '%TEST%'
      AND U.REFERENCED_NAME = A.TABLE_NAME(+)

edited Oct 23 '13 at 11:58

answered Oct 23 '13 at 11:08

Srini V

11,045
14
66
89

@Moudiz Update the query... Check now – Srini V Oct 23 '13 at 11:58
realspirituals please can you give me a documentation or you can explain to me when do we use the (+) or the (-) ? – Moudiz Oct 23 '13 at 14:27
There is no (-) only (+) read this: http://stackoverflow.com/questions/18891148/oracle-joins-comparison-between-conventional-vs-join-operator-keyword THis has more links inside – Srini V Oct 23 '13 at 14:28
The `(+)` syntax for outer joins is old and has limitations. Oracle itself suggests that `JOIN` syntax is used. See http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation/1193722#1193722 – ypercubeᵀᴹ Oct 23 '13 at 14:35
@ypercube refer my commented link. – Srini V Oct 23 '13 at 14:39
I have read the link you provided. But why do you answer the question with code that Oracle recommends not to use? – ypercubeᵀᴹ Oct 23 '13 at 14:41

score 1 · Answer 2 · answered Oct 23 '13 at 12:03

Use an OUTER JOIN:

FROM  
    USER_DEPENDENCIES u 
  LEFT OUTER JOIN
    user_tables a  
      ON u.REFERENCED_NAME = a.TABLE_NAME
WHERE 
    u.name ='RANDOM_PROCEDURE_NAME'  ;

All rows from the (left) table USER_DEPENDENCIES, no matter if they have a matching row at the user_tables or not.

Thorsten Kettner · Accepted Answer · 2013-10-23T12:12:02.867

This gives you all dependencies on your object. For tables and procedures it shows which of them exist in your database schema:

select dep.name , dep.type , dep.referenced_name , dep.referenced_type,
case 
  when dep.referenced_type = 'TABLE' and tab.table_name is not null then
    'TABLE EXISTS'
  when dep.referenced_type = 'PROCEDURE' and pro.procedure_name is not null then
    'PROCEDURE EXISTS'
end as lookup
from user_dependencies dep
left outer join user_tables tab on (dep.referenced_type = 'TABLE' and tab.table_name = dep.referenced_name) 
left outer join user_procedures pro on (dep.referenced_type = 'PROCEDURE' and pro.procedure_name = dep.referenced_name) 
where dep.name ='RANDOM_PROCEDURE_NAME';

this query was more efficient for me :) – Moudiz Oct 23 '13 at 14:27 — Moudiz, Oct 23 '13 at 14:27

score 1 · Answer 4 · answered Oct 23 '13 at 12:40

Please use LEFT OUTER JOIN instead of INNER JOIN. Replace WHERE with ON your QUERY like this:

SELECT U.NAME , U.TYPE , U.REFERENCED_NAME , U.REFERENCED_TYPE,
CASE 
WHEN U.REFERENCED_TYPE= 'TABLE' and A.TABLE_NAME= U.REFERENCED_NAME 
  THEN 'TABLE EXISTS'
WHEN U.REFERENCED_TYPE= 'PROCEDURE' and A.TABLE_NAME= U.REFERENCED_NAME 
   THEN 'PROCEDURE EXISTS'
END
FROM  USER_DEPENDENCIES U 
LEFT JOIN user_tables a  
ON U.name ='RANDOM_PROCEDURE_NAME' and U.REFERENCED_NAME = A.TABLE_NAME;

Hope this will help you. Thanks. :)

Outer join in case statement

4 Answers4