时间戳

首页 > 解决方案 > Double Pivot across columns is failing

oracle - Double Pivot across columns is failing

问题描述

I wrote a code which has got two pivots in the query but it is failing to give any output.The error is : ORA-00904: "THESEUS_ACTIVE": invalid identifier. Could anyone help me on this

SELECT company_id
, MAX(alt_rec_date) alt_rec_date
, rec_date
, MAX(STANDARD) STANDARD
, MAX(THESEUS) THESEUS
, CAST(NVL(MAX(STANDARD_Active), 0) AS NUMBER(1)) STANDARD_Active
, CAST(NVL(MAX(THESEUS_Active), 0) AS NUMBER(1)) THESEUS_Active  --the 
error is here in max(theseus_active)--
FROM (
**SELECT RH.company_id
      ,MAX(RH.alt_rec_date) alt_rec_date
      ,IT.type_code
      ,RH.alt_rec_code rec_code
      ,RH.rec_date
      , CAST(ITC.active AS NUMBER(10)) active
      ,IT.type_code || '_Active' active_type_code
FROM tblInvestTypeRecHist RH
    JOIN tblInvestType IT ON RH.type_code = IT.type_code
    JOIN (
        SELECT company_id, MAX(rec_date) rec_date
        FROM tblInvestTypeRecHist GROUP BY company_id
        ) LR
    ON RH.company_id = LR.company_id AND RH.rec_date = LR.rec_date
    JOIN tblInvestTypeComp ITC 
    ON RH.company_id = ITC.company_id AND IT.type_code = ITC.type_code
GROUP BY RH.company_id
      ,IT.type_code
      ,RH.alt_rec_code 
      ,RH.rec_date
      ,ITC.active**

) Data

PIVOT (
MAX(rec_code)
FOR type_code IN
('STANDARD','THESEUS')
)  pvt

PIVOT (
MAX(active)
FOR active_type_code IN
('STANDARD_Active','THESEUS_Active')
)  pvt

GROUP BY company_id, rec_date;

How can i resolve this?

标签： oraclepivot

解决方案

您没有为透视列指定别名，因此默认情况下，它们最终作为带引号的标识符设置为要匹配的值；因此您可以将查询的开头更改为：

SELECT company_id
, MAX(alt_rec_date) alt_rec_date
, rec_date
, MAX("'STANDARD'") STANDARD
, MAX("'THESEUS'") THESEUS
, CAST(NVL(MAX("'STANDARD_Active'"), 0) AS NUMBER(1)) STANDARD_Active
, CAST(NVL(MAX("'THESEUS_Active'"), 0) AS NUMBER(1)) THESEUS_Active
FROM (
...

注意双引号和单引号，并且大小写与您之前生成的字符串完全匹配；并且您必须将引号应用于第一个枢轴和第二个枢轴的列。

或者，可能更易读，给旋转列别名：

SELECT company_id
, MAX(alt_rec_date) alt_rec_date
, rec_date
, MAX(STANDARD) STANDARD
, MAX(THESEUS) THESEUS
, CAST(NVL(MAX(STANDARD_ACTIVE), 0) AS NUMBER(1)) STANDARD_Active
, CAST(NVL(MAX(THESEUS_ACTIVE), 0) AS NUMBER(1)) THESEUS_Active
FROM (
...
) Data

PIVOT (
MAX(rec_code)
FOR type_code IN
('STANDARD' as standard,'THESEUS' as theseus)
)  pvt

PIVOT (
MAX(active)
FOR active_type_code IN
('STANDARD_Active' as standard_active,'THESEUS_Active' as theseus_active)
)  pvt

GROUP BY company_id, rec_date;

我不确定您是否真的需要两个支点；如果没有样本数据和预期结果，我很可能会遗漏一些东西，但看起来你可以做到：

...
) Data
PIVOT (
  MAX(rec_code), MAX(active) as active
  FOR type_code IN ('STANDARD' as standard,'THESEUS' as theseus)
)
GROUP BY company_id, rec_date;

整个事情看起来可能会被简化。

推荐阅读