Блог

У меня есть следующий код, который я пытаюсь выполнить:

     SELECT 
        '25597' AS company_id,    
        to_date('03/20/2019','mm/dd/yyyy') AS rec_date,
        P.*

    FROM

    (  
    SELECT D.FieldYear -- This column does not get loaded - just for comparison with the year column
           ,F.ColumnName Field 
           ,CASE F.IsPercent
            WHEN 1 THEN D.FieldValue * 100.0
                   ELSE D.FieldValue
            END FieldValue
     FROM (SELECT 
         CAST(FieldYear AS Number) FieldYear
        , FieldType
        , CASE FieldValue 
            WHEN n'' THEN NULL
            ELSE CAST (FieldValue AS Number(38,8))
          END FieldValue
    FROM 
       (SELECT IncomeStatementField AS FieldType
        ,Y1 as "2013"
        ,Y2 as "2014"
        ,Y3 as "2015"
        ,Y4 as "2016"
        ,Y5 as "2017"
        ,Y6 as "2018" 
        ,Y7 as "2019"
        ,Y8 as "2020"
        ,Y9 as "2021"
        ,Y10 as "2022"
        ,Y11 as "2023"
        ,Y12 as "2024"
        ,Y13 as "2025"
        ,Y14 as "2026"
        ,Y15 as "2027"
        ,Y16 as "2028"
        ,Y17 as "2029"
        ,Y18 as "2030"
        ,Y19 as "2031"
        ,Y20 as "2032"
        ,Y21 as "2033"
        ,Y22 as "2034"
        ,Y23 as  "2035"
        ,Y24 as "2036"
        ,Y25 as "2037"
        ,Y26 as "2038"
        ,Y27 as "2039"
        ,Y28 as "2040"
        ,Y29 as "2041"
        ,Y30 as "2042"
        ,Y31 as "2043"
        ,Y32 as "2044"
        ,Y33 as "2045"
        ,Y34 as "2046"
        ,Y35 as "2047"

      FROM TBLTMPLTS_LOAD_INCSTATEMT_RAW) 
    UNPIVOT
       (FieldValue FOR FieldYear IN 
        ("2013"
        ,"2014"
        ,"2015"
        ,"2016"
        ,"2017"
        ,"2018"
        ,"2019"
        ,"2020"
        ,"2021"
        ,"2022"
        ,"2023"
        ,"2024"
        ,"2025"
        ,"2026"
        ,"2027"
        ,"2028"
        ,"2029"
        ,"2030"
        ,"2031"
        ,"2032"
        ,"2033"
        ,"2034"
        ,"2035"
        ,"2036"
        ,"2037"
        ,"2038"
        ,"2039"
        ,"2040"
        ,"2041"
        ,"2042"
        ,"2043"
        ,"2044"
        ,"2045"
        ,"2046"
        ,"2047"
    )
    )) D 
     JOIN tblTemplates_Fields F ON D.FieldType = F.FieldName

     WHERE F.TemplateSection = 'Income Statement'
     AND F.TemplateType In ('All', 'ALL','Industrial')
     AND    (TemplateVersion = '1' or (TemplateVersion is null and '1' <> 'V8')))  P

     PIVOT
    (
    SUM(FieldValue) 
    FOR Field IN 
        ('year',
        'sales',
        'ebitda_margin',
        'op_margin',
        'pretax_charges',
        'net_int_exp',
        'pretax_margin',
        'tax_rate','after_tax_income_gaap',
        'pretax_income',
        'net_income_adjustment',
        'ret_income',
        'shares',
        'eps',
        'dps',
        'accrual_adj_to_net_income',
        'minority_interest',
        'cash_movement',
        'working_cap',
        'int_rate_cash',
        'net_wc',
        'net_wc_to_sales',
        'sales_growth',
        'int_rate_debt',
        'roce',
        'net_debt',
        'free_cash_flow',
        'stock_issuance','debt_issuance','disposals','capex','cash_flow_from_ops','amortization','depreciation','pref_div','net_income_adjusted','capex_sales_check'
        )
    )  pvt
  

Когда я выполняю приведенный выше запрос, я получаю ошибку P как недопустимый идентификатор, и когда я удаляю P из запроса в обоих местах, я получаю ошибку отсутствующего выражения. Похоже, это связано с СВОДКОЙ.

Все выражение сводной таблицы должно иметь псевдоним, а не только компонент таблицы.

Использование таблиц SCOTT / TIGER для более простого примера:

 select p.*
from   ( select job, deptno, sal from emp )
pivot  ( sum(sal) for deptno in (10, 20, 30, 40) ) p;

JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
CLERK           1300       1900        950 
SALESMAN                              5600 
PRESIDENT       5000                       
MANAGER         2450       2975       2850 
ANALYST                    6000