CODEWITHSUNDEEP
sqloracle-database
Somnath Goswami
Somnath Goswami

Reputation: 112

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?

Upvotes: 0

Views: 7184

Answers (4)

Sentinel
Sentinel

Reputation: 6449

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.

Upvotes: 0

MT0
MT0

Reputation: 168450

Something like this (although without a description of your tables, the exact join conditions or any sample data its hard to give a more precise answer):

SELECT E.EMP_NO,
       E.NAME,
       COALESCE( P.EMP_TYPE, C.EMP_TYPE ) AS EMP_TYPE
       COALESCE( P.DATE_JOINED, C.DATE_JOINED ) AS DATE_JOINED
FROM   Employee E
       LEFT OUTER JOIN
       Permanent_Emp P
       ON ( E.EMP_TYPE_CODE > 10 AND E.EMP_NO = P.EMP_NO )
       LEFT OUTER JOIN
       Contract_Emp C
       ON ( E.EMP_TYPE_CODE <= 10 AND E.EMP_NO = C.EMP_NO )
ORDER BY
       E.EMP_NO DESC

Upvotes: 2

Allan
Allan

Reputation: 17429

There is no way to conditionally add tables to a query in static SQL. If the relevant columns in Permanent_Emp and Contract_Emp are roughly equivalent, you could use a union in a sub-query.

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     e.employee_id = se.employee_id
             AND (   (e.emp_type_code >  10 AND source_indicator = 'P')
                  OR (e.emp_type_code <= 10 AND source_indicator = 'C'))

Upvotes: 1

koushik veldanda
koushik veldanda

Reputation: 1107

use your case in select and join both tables

as

SELECT case when 1 then a.column
            when 2 then b.column
       end
from table c
join table a
on 1=1
join table2 b
on 1=1

but you cant use case while joining. its better to join both tables and in select use case statement with conditions as per your requirement

Upvotes: 1

Related Questions