How to join table with result column in oracle

Tasbin Islam 0
Viewed 26

I used case when to get a column named 'code' in oracle. Now I want to get another column from a table which need to be joined with the result 'code' column. Example:

select 
(case when field='A' then '001'
when field='B' then '002'
end) code
from table_code

Now I want to get another column 'code_name' in this query from table 'names' containing data as

code: 001= code_name: aa & code: 002= code_name: bb
case datatable oracle join sql
1 Answers

WITH clause is here just to generate some sample data, and as such, it is not a part of the answer.
Just guessing what you are asking for, but if I got it right the answer is below.
First the sample data:

WITH
    ids_data AS
        (
            Select 'A' "CODE_ID", 'ABCD_123' "SOME_FLD", 10 "AMOUNT" From Dual Union All
            Select 'B' "CODE_ID", 'BCD_123' "SOME_FLD", 25 "AMOUNT" From Dual Union All
            Select 'C' "CODE_ID", 'CD_123' "SOME_FLD", 10 "AMOUNT" From Dual Union All
            Select 'D' "CODE_ID", 'D_123' "SOME_FLD", 30 "AMOUNT" From Dual Union All
            Select 'E' "CODE_ID", 'X123' "SOME_FLD", 15 "AMOUNT" From Dual U
        ),
    names_data AS
        (
            Select '001' "CODE_ID", 'Code Name 001' "CODE_NAME" From Dual Union All
            Select '002' "CODE_ID", 'Code Name 002' "CODE_NAME" From Dual Union All
            Select '003' "CODE_ID", 'Code Name 003' "CODE_NAME" From Dual Union All
            Select '004' "CODE_ID", 'Code Name 004' "CODE_NAME" From Dual Union All
            Select '005' "CODE_ID", 'Code Name 005' "CODE_NAME" From Dual U
        )

The answer is - just use the CASE statement in the join condition ON clause like you did it in the select clause.

SELECT
    i.CODE_ID "CODE_ID",
    CASE 
        WHEN i.CODE_ID = 'A' THEN '001'
        WHEN i.CODE_ID = 'B' THEN '002' 
        WHEN i.CODE_ID = 'C' THEN '003'
        WHEN i.CODE_ID = 'D' THEN '004'
        WHEN i.CODE_ID = 'E' THEN '005' 
    ELSE
        '000'
    END "CODE_ID_2",
    n.CODE_NAME "CODE_NAME",
    i.SOME_FLD "SOME_FFLD",
    i.AMOUNT "AMOUNT"
FROM
    ids_data i
INNER JOIN
    names_data n 
        ON(n.CODE_ID =  CASE 
                            WHEN i.CODE_ID = 'A' THEN '001'
                            WHEN i.CODE_ID = 'B' THEN '002' 
                            WHEN i.CODE_ID = 'C' THEN '003'
                            WHEN i.CODE_ID = 'D' THEN '004'
                            WHEN i.CODE_ID = 'E' THEN '005' 
                        ELSE
                            '000'
                        END)

Here is the result

--
--  R e s u l t :
--
--  CODE_ID CODE_ID_2 CODE_NAME     SOME_FFLD     AMOUNT
--  ------- --------- ------------- --------- ----------
--  A       001       Code Name 001 ABCD_123          10 
--  B       002       Code Name 002 BCD_123           25 
--  C       003       Code Name 003 CD_123            10 
--  D       004       Code Name 004 D_123             30 
--  E       005       Code Name 005 X123              15
d r0
Related