Bdtjtk

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?

You aren't specifying aliases for the pivoted columns, so by default they end up as quoted identifiers set to the values being matched; so you can change the start of your query to:

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 (

...

Notice both the double- and single-quotes, and the case exactly matching the generated string you produced earlier; and that you have to apply quotes to the columns from the first pivot as well as the second pivot.

Alternatively, and probably more readably, give the pivoted columns aliases:

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;

I'm not sure you actually need two pivots here; without sample data and expected results I may well be missing something, but it looks like you might be able to do:

...

) 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;

The whole thing looks like it could probably be simplified though.