Somnath Goswami Somnath Goswami - 6 months ago 27
SQL Question

can we have CASE expression/case result as a join table name in oracle

I have 3 tables say Employee, Permanent_Emp and Contract_Emp

SELECT E.EMP_NO,
E.NAME,
JET.EMP_TYPE,
JET.DATE_JOINED
FROM Employee E
LEFT OUTER JOIN
/* Here Join Table Name(JET) it can be Permanent_Emp or Contract_Emp
which i want as a result of my case expression. */
ON (some condition here) ORDER BY E.EMP_NO DESC


case expression:

CASE
WHEN (E.EMP_TYPE_CODE >10 )
THEN
Permanent_Emp JET
ELSE
Contract_Emp JET
END


Note: table and column names are just for an example to understand requirement.

how can i have join table name from a case expression?

Answer

Using Alan's query as a starting point you can still use a case statement, just move it to the join condition:

 SELECT *
   FROM employee e
   JOIN (
         SELECT employee_id
              , relevant_column
              , 'P' AS source_indicator
           FROM permanent_emp
         UNION ALL
         SELECT employee_id
              , relevant_column
              , 'C' AS source_indicator
           FROM contract_emp
      ) se
     ON se.employee_id   = e.employee_id
    and se.source_indicator = case when e.emp_type_code > 10
                                   then 'P'
                                   else 'C'
                              end

The only difference between this query and Allan's is the use of a case statement instead of an or statement.