'Dynamic Column Name for a Derived Column - ORACLE SQL

I'm trying to set a dynamic column name on my query using "select from dual". Is this possible? If not, kindly recommend alternatives for me to achieve this. I need this on a normal select query, not by using a stored procedure.

I'm trying to achieve the query below:

SELECT  A.NO
        ,A.SUB_NO
        ,A.DCY
        ,A.STATE

        ,NVL(TO_CHAR(M1.NUMERATOR),'0') AUG_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M1.DENOMINATOR),'0') AUG_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M2.NUMERATOR),'0') JUL_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M2.DENOMINATOR),'0') JUL_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M3.NUMERATOR),'0') JUN_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M3.DENOMINATOR),'0') JUN_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' FROM DUAL

        ,M1.M1_CALC
        ,M2.M2_CALC
        ,M3.M3_CALC

FROM A, M1,M2,M3;

Thank you in advance for your help.

sqloracleplsql


Solution 1:[1]

I'm pretty sure the closest you're going to get to what you want is by using a UNION ALL.

SELECT NULL no,
       NULL sub_no,
       NULL dcy,
       NULL state,
       TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' AUG_NUM,
       TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' AUG_DEN,
       TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' JUL_NUM,
       TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' JUL_DEN,
       TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' JUN_NUM,
       TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' JUN_DEN,
       NULL M1_CAL,
       NULL M2_CALC,
       NULL M3_CALC
FROM dual
UNION ALL
SELECT  A.NO
        ,A.SUB_NO
        ,A.DCY
        ,A.STATE

        ,NVL(TO_CHAR(M1.NUMERATOR),'0') AUG_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M1.DENOMINATOR),'0') AUG_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M2.NUMERATOR),'0') JUL_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M2.DENOMINATOR),'0') JUL_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M3.NUMERATOR),'0') JUN_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M3.DENOMINATOR),'0') JUN_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' FROM DUAL

        ,M1.M1_CALC
        ,M2.M2_CALC
        ,M3.M3_CALC
FROM A, M1,M2,M3;

Solution 2:[2]

WITH A AS
    (
        SELECT  
            636 "NO", 159 "SUB_NO", To_Char(SYSDATE, 'yyyy') "DCY", 'State 1' "STATE"
        FROM dual       --This could be records from any of your data tables
      UNION
        SELECT  
            1272 "NO", 318 "SUB_NO", To_Char(SYSDATE, 'yyyy') "DCY", 'State 2' "STATE"
        FROM dual       --This could be records from any of your data tables
    )
SELECT  
    NO, SUB_NO, DCY, STATE,
    MONTH_MINUS_2_NUM, MONTH_MINUS_2_DEN,
    MONTH_MINUS_3_NUM, MONTH_MINUS_3_DEN,
    MONTH_MINUS_4_NUM, MONTH_MINUS_4_DEN,
    M1_CALC, M2_CALC, M3_CALC
FROM 
    a
MODEL
    DIMENSION BY( DCY, STATE)
    MEASURES( NO, SUB_NO,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_2_NUM, 
              CAST('x' as VarChar2(20)) as MONTH_MINUS_2_DEN,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_3_NUM,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_3_DEN,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_4_NUM,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_4_DEN,
              0 as M1_CALC,
              0 as M2_CALC,
              0 as M3_CALC
             )
    RULES
    (
      MONTH_MINUS_2_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM',
      MONTH_MINUS_2_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN',
      MONTH_MINUS_3_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM',
      MONTH_MINUS_3_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN',
      MONTH_MINUS_4_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM',
      MONTH_MINUS_4_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN',
  -- Dynamic Calculations -> you can calculate prety much anything you want
      M1_CALC[2022, 'State 1'] = NO[CV(), CV()] / 2,
      M2_CALC[2022, 'State 1'] = NO[CV(), CV()] / 3,
      M3_CALC[2022, 'State 1'] = NO[CV(), CV()] / 4,
      M1_CALC[2022, 'State 2'] = NO[CV(), CV()] / 2,
      M2_CALC[2022, 'State 2'] = NO[CV(), CV()] / 3,
      M3_CALC[2022, 'State 2'] = NO[CV(), CV()] / 4
    )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Acroyear
Solution 2 d r

Related Questions