Selection of certain columns in oracle

Question

I have a table name TABLE1 having 4 columns (ID, LINE, COST, CODE)

  CREATE TABLE TABLE1 ( ID NUMBER(4),  LINE NUMBER(3), COST NUMBER(4), CODE VARCHAR2(3) );

  INSERT INTO TABLE1 VALUES(101, 1, 40, 'ABC' );
  INSERT INTO TABLE1 VALUES(101, 1, 50, 'DEF' );
  INSERT INTO TABLE1 VALUES(102, 2, 30, 'CDE' );
  INSERT INTO TABLE1 VALUES(102, 2, 20, 'ECD' );
  INSERT INTO TABLE1 VALUES(103, 3, 5, 'BCD' );
  INSERT INTO TABLE1 VALUES(103, 3, 5, 'BCD' );

  ID     LINE   COST    CODE
  --------------------------
  101     1     40       ABC
  101     1     50       DEF
  102     2     30       CDE
  102     2     20       ECD
  103     3     10       BCD

From the above table you can see that I have taken a sum of cost of each ID with respective to each LINE...

 For ID = 101 and LINE = 1 -> SUM(COST) = 40+50 = 90 
 For ID = 102 and LINE = 2 -> SUM(COST) = 30+20 = 50
 For ID = 103 and LINE = 3 -> SUM(COST) = 10 = 10 

---

I have a TABLE2 having 6 columns (ID,LINE, COST_PR, PR_CODE, COST_SC,SC_CODE)

 CREATE TABLE TABLE2 ( ID NUMBER(4), LINE NUMBER(3), COST_PR NUMBER(4), PR_CODE VARCHAR2(3), COST_SC NUMBER(4), SC_CODE VARCHAR2(3) );

 INSERT INTO TABLE2 VALUES(101, 1, 90, 'ABC', 100, 'CBA');
 INSERT INTO TABLE2 VALUES(102, 2, 60, 'CDE', 50,  'EDC');
 INSERT INTO TABLE2 VALUES(103, 3, 10, 'BCD', 10,  'BCD');

 ID    LINE    COST_PR    PR_CODE    COST_SC     SC_CODE
 --------------------------------------------------------
 101    1       90          ABC       100         CBA
 102    2       60          CDE        50         EDC
 103    3       10          BCD        10         BCD

---

I have a TABLE3 with 2 columns (SEC, PIN)

 CREATE TABLE TABLE3( SEC VARCHAR2(3), PIN VARCHAR2(2) );

 INSERT INTO TABLE3 VALUES ('ABC', 'A1' );
 INSERT INTO TABLE3 VALUES ('DEF', 'A2' );
 INSERT INTO TABLE3 VALUES ('CDE', 'A3' );
 INSERT INTO TABLE3 VALUES ('ECD', 'A4' );
 INSERT INTO TABLE3 VALUES ('BCD', 'A5' );
 INSERT INTO TABLE3 VALUES ('EDC', 'A7' );
 INSERT INTO TABLE3 VALUES ('CBA', 'A8' );

The resultant table be like

 ID   LINE      PR_CODE  PR_PIN    SC_CODE  SC_PIN  
---------------------------------------------------
101    1         ABC       A1      CBA       A8
101    1         DEF       A2      CBA       A8
102    2         CDE       A3      CDE       A3
102    2         CDE       A3      ECD       A4
103    3         BCD       A5      BCD       A5   

The above resultant logic is

 Case1: From TABLE1, For ID = 101 & LINE = 1, SUM(COST) = 40+50 = 90
     we need to compare this SUM(COST) with COST_PR and COST_SC of TABLE2,
    In this case, SUM(COST) = COST_PR 
    so we need to populate CODE values from TABLE1 in PR_CODE columns and populate SC_CODE 
    values from TABLE2 as it is (You can see in first 2 rows of resultant table)

 Case2: From TABLE1, For ID = 102 & LINE = 2, SUM(COST) = 30+20 = 50
     we need to compare this SUM(COST) with COST_PR and COST_SC of TABLE2,
    In this case, SUM(COST) = COST_SC 
    so we need to populate CODE values from TABLE1 in SC_CODE columns and populate PR_CODE 
    values from TABLE2 as it is (You can see in 3rd & 4th rows of resultant table)

 Case3: From TABLE1, For ID = 103 & LINE = 3, SUM(COST) = 10
     we need to compare this SUM(COST) with COST_PR and COST_SC of TABLE2,
    In this case, SUM(COST) = COST_PR = COST_SC 
    so we need to populate CODE values from TABLE1 in PR_CODE columns  and SC_CODE 
    columns  (You can see in last row of resultant table)

 PR_PIN and SC_PIN is populated by SEC column values of TABLE3 with PR_CODE and SC_CODE (PR_CODE = SEC and also SC_CODE = SEC)

Answer 1

You could use this query to return result as your describe logic, assume that your expected result is typo as I comment

WITH tmp1 AS 
(
    SELECT DISTINCT t.*, 
        SUM(cost) OVER (PARTITION BY id, line) AS sum_code
    FROM table1 t 
)
SELECT t1.id, t1.line,
    CASE 
        WHEN t2.cost_pr = t1.sum_code THEN t1.code 
        ELSE t2.pr_code 
    END AS pr_code,
    CASE 
        WHEN t2.cost_sc = t1.sum_code THEN t1.code 
        ELSE t2.sc_code 
    END AS sc_code
FROM tmp1 t1
INNER JOIN table2 t2
ON t1.id = t2.id AND t1.line = t2.line
ORDER BY t1.id, t1.line;

Tested in sqlfiddle

With your edited question that add table3, you could either use my first query as a CTE and join with table3 two times by pr_code and sc_code each, or if you want to use CASE instead, then join with table3 three times as follow:

WITH tmp1 AS 
(
    SELECT DISTINCT t.*, 
        SUM(cost) OVER (PARTITION BY id, line) AS sum_code
    FROM table1 t 
)
SELECT t1.id, t1.line,
    CASE 
        WHEN t2.cost_pr = t1.sum_code THEN t1.code 
        ELSE t2.pr_code 
    END AS pr_code,
    CASE 
        WHEN t2.cost_pr = t1.sum_code THEN t31.pin 
        ELSE t32.pin
    END AS pr_pin,
    CASE 
        WHEN t2.cost_sc = t1.sum_code THEN t1.code 
        ELSE t2.sc_code 
    END AS sc_code,
    CASE 
        WHEN t2.cost_sc = t1.sum_code THEN t31.pin
        ELSE t33.pin 
    END AS sc_pin
FROM tmp1 t1
INNER JOIN table2 t2
ON t1.id = t2.id AND t1.line = t2.line
INNER JOIN table3 t31
ON t1.code = t31.sec
INNER JOIN table3 t32
ON t2.pr_code = t32.sec 
INNER JOIN table3 t33
ON t2.sc_code = t33.sec 
ORDER BY 1, 2, 3, 5;

Tested in sqlfiddle