CODEWITHSUNDEEP
oracle-databaseplsql
jeyong kim
jeyong kim

Reputation: 11

How can I extract columns name from row value of another table(oracle sql)?

I have 2 tables:

table1

no a b c
x1 2 3 4
x2 10 11 12
x3 20 21 22

table2

from_val in_out cf_pv term
a out cf b
b out pv b
c in cf e

Define sum_out is sum of a, b, c in table1 with condition in_out='out' in table2 and sum_cf is sum of a, b, c in table1 with condition cf_pv='cf' in table2.

Shortly, values of from_val in table2 are columns name i.e. a, b, c in table1.

How can I extract and calculate sum_out or sum_cf of every no in Oracle?

sum_out of x1 = 2 + 3
sum_out of x2 = 10 + 11
sum_out of x3 = 20 + 21

sum_cf of x1 = 2 + 4
sum_cf of x2 = 10 + 12
sum_cf of x3 = 20 + 22

Thanks!

'''''''''''''''''''''''''''''''''''''''''''''

in additional,

i want to calculate

sum_out and cf of x1= 2 (=a)
sum_out and cf of x2= 10 (=b)
sum_out and cf of x3= 20 (=c)

Upvotes: 0

Views: 75

Answers (1)

d r
d r

Reputation: 7776

  1. Sample data
WITH
    tbl_1 AS
        (
            Select 'x1' "COL_NO",  2 "A",  3 "B",  4 "C" From Dual Union All
            Select 'x2' "COL_NO", 10 "A", 11 "B", 12 "C" From Dual Union All
            Select 'x3' "COL_NO", 20 "A", 21 "B", 22 "C" From Dual 
        ),
    tbl_2 AS
        (
            Select 'A' "FROM_VAL",  'out' "IN_OUT",  'cf' "CF_PV",  'begin' "TERM" From Dual Union All
            Select 'B' "FROM_VAL",  'out' "IN_OUT",  'pv' "CF_PV",  'begin' "TERM" From Dual Union All
            Select 'C' "FROM_VAL",  'in'  "IN_OUT",  'cf' "CF_PV",  'end' "TERM" From Dual          
        ),
  1. Create CTE (formulas) that generates formulas for IN_OUT = 'out' and For CF_PV = 'cf'
  formulas AS
    (
        Select
            CASE WHEN IN_OUT = 'out' THEN IN_OUT END "IN_OUT", 
            LISTAGG(FROM_VAL, ' + ') WITHIN GROUP (ORDER BY FROM_VAL) OVER(PARTITION BY IN_OUT) "IN_OUT_FORMULA", 
            CASE WHEN CF_PV = 'cf' THEN CF_PV END "CF_PV",  
            LISTAGG(FROM_VAL, ' + ') WITHIN GROUP (ORDER BY FROM_VAL) OVER(PARTITION BY CF_PV) "CF_PV_FORMULA"
        From
            tbl_2
    ),
IN_OUT IN_OUT_FORMULA CF_PV CF_PV_FORMULA
C cf A + C
out A + B cf A + C
out A + B B
  1. Another CTE (grid) to connect COL_NO to formulas
  grid AS
    (
        Select   
            t1.COL_NO,
            CASE WHEN f1.IN_OUT = 'out' THEN f1.IN_OUT END "IN_OUT", CASE WHEN f1.IN_OUT = 'out' THEN f1.IN_OUT_FORMULA END "IN_OUT_FORMULA", 
            CASE WHEN f1.CF_PV = 'cf' THEN f1.CF_PV END "CF_PV", CASE WHEN f1.CF_PV = 'cf' THEN f1.CF_PV_FORMULA END "CF_PV_FORMULA"
        From
            tbl_1 t1
        Left Join
            formulas f1 ON(f1.IN_OUT Is Not Null AND f1.CF_PV Is Not Null)
   )
COL_NO IN_OUT IN_OUT_FORMULA CF_PV CF_PV_FORMULA
x1 out A + B cf A + C
x2 out A + B cf A + C
x3 out A + B cf A + C
  1. Main SQL to get the final result
    SELECT
        g.COL_NO,
        g.IN_OUT,
        g.IN_OUT_FORMULA,
        CASE WHEN g.IN_OUT = 'out' And INSTR(IN_OUT_FORMULA, 'A') > 0 THEN A ELSE 0 END + 
        CASE WHEN g.IN_OUT = 'out' And INSTR(IN_OUT_FORMULA, 'B') > 0 THEN B ELSE 0 END + 
        CASE WHEN g.IN_OUT = 'out' And INSTR(IN_OUT_FORMULA, 'C') > 0 THEN C ELSE 0 END "CALC_OUT",
        --
        g.CF_PV,
        g.CF_PV_FORMULA,
        CASE WHEN g.CF_PV = 'cf' And INSTR(CF_PV_FORMULA, 'A') > 0 THEN A ELSE 0 END + 
        CASE WHEN g.CF_PV = 'cf' And INSTR(CF_PV_FORMULA, 'B') > 0 THEN B ELSE 0 END +  
        CASE WHEN g.CF_PV = 'cf' And INSTR(CF_PV_FORMULA, 'C') > 0 THEN C ELSE 0 END "CALC_CF"
    FROM
        grid g
    INNER JOIN
        tbl_1 t1 ON(g.COL_NO = t1.COL_NO)

R e s u l t :

COL_NO IN_OUT IN_OUT_FORMULA CALC_OUT CF_PV CF_PV_FORMULA CALC_CF
x1 out A + B 5 cf A + C 6
x2 out A + B 21 cf A + C 22
x3 out A + B 41 cf A + C 42

Upvotes: 1

Related Questions