CODEWITHSUNDEEP
sqloracle-databasepivot
SMS
SMS

Reputation: 61

PL-SQL: ORA-00904 - Invalid Identifier - Column in Select and Pivot Clause

Have code that works to create output below, and cannot for the life of me debug the pivot to take "Tax Status" to each have their own column. I remove the "ORDER" and "GROUP" clauses and try this as a pivot, but it still yields "ORA-00904: "Tax Status": invalid identifier". Depending on what I've done it gets made at the initial select at times, others it gets angry about the pivot and if Im using single or double quotes.

Pivot Struggling With:

pivot
(
    min('Total Value') 
    for ("Tax Status")
    in ('Exempt from Taxation' as "Exempt", 'Taxable' as "Taxable", 'Tax Agreement - Operator of a Public Utility' as "Tax Agreement",
    '"Assessed Person" Tax Agreement' as "Tax Agreement", 'Grant in Place of Tax' as "Grant", 'Council Tax Cancellation or Refund' as "Council Cancel/Refund")
)

Working Query that Produces Output Below:

SELECT "Team", "Tax Status", sum("Total Value") "Total Value"
FROM 
    (
    select (A.account_roll_number) "Roll Number", ALU.DESCRIPTION "Assessor", A.account_total_property_value as "Total Value",
    TaxLU.DESCRIPTION "Tax Status",
    
    CASE
        when A.assessor_id in ('ATHENDRATA', 'BRTHOMPSON', 'FTACIUNE', 'HPHILLIPS', 'JDCHU', 'JRYOUNG1', 'MHARTMANN', 'NCCHAN', 'RLEE5', 'SBERZINS') then 'Industrial'
        when A.assessor_id in ('ASMTDWA','ASMTEB', 'ASMTWS', 'BBROCKLEBANK', 'CCHICHAK', 'CYMAU', 'GJONES4', 'IYPAU', 'JTGREER', 'KHOUSTON', 'LGMORRISON', 'MMCKENZIE1', 'MWALZ', 'SKUANG', 'STBAZIN', 'TKNGUYEN1', 'ASHIELDS') then 'Single Residential'
        when A.assessor_id in ('BTANG', 'CMACMILLAN1', 'DGENCARELLI', 'EWU1', 'JWEHLER', 'LMDUNBAR', 'LWONG4', 'MGULOWSKA', 'RLEE1', 'SHAMMOUD', 'SLTURNER', 'YWANG') then 'Multi Residential'
        when A.assessor_id in ('CMULENGA', 'EPOPOVICS', 'GFULLER', 'IMCDERMOTT', 'JERMUBE', 'JNSTEVENSON', 'JPLEPINE', 'KBUCKRY', 'KDALMHJELL', 'NPHAM1', 'PGKERSEY', 'SMSAMPLE') then 'Special Purpose and Land'
        when A.assessor_id in ('ASMTHN', 'DCARSON', 'DLIDGREN', 'DMCCORD', 'EBORISENKO', 'HYAU1', 'MCTRIMBLE', 'RJTHARAKAN', 'TBJOHNSON1', 'VWONG1', 'WGIBBS', 'YYE', 'AVPETERS') then 'Office'
        when A.assessor_id in ('AKEAST', 'BLTHOMPSON', 'BNELSON1', 'JCRUJI', 'JWONG1', 'KGARDINER', 'KMHAUT', 'NTNGUYEN', 'RTLUCHAK', 'SGILL3', 'THEGER1', 'TJLUDLOW', 'ZRGEIB') then 'Retail'
        else 'Other'
    END as "Team"
    
    from REP_DBA.AB000_ACCOUNT A
    
    join REP_DBA.LU_ASMT_ASSIGN_ASSESSOR ALU
    on A.assessor_id = ALU.code
    
    -- Decode the Tax Status from the LU Table
    join rep_dba.LU_ASMT_ACCT_TAX_STATUS TaxLU
    on TaxLU.CODE = A.account_tax_status
    
    where A.rollyear = :rollyear
    and A.account_type = 'P'
    and A.account_status = 'AP'
    and A.account_total_property_value is not null
    ORDER BY account_total_property_value DESC
)
GROUP BY "Team", "Tax Status"
ORDER BY "Team"

Output before Pivot

Working Output

Upvotes: 1

Views: 424

Answers (1)

Popeye
Popeye

Reputation: 35920

You must use double quotes for the column in PIVOT as follows:

Select * from
(
SELECT "Team", "Tax Status", sum("Total Value") "Total Value"
FROM 
    (
    select (A.account_roll_number) "Roll Number", ALU.DESCRIPTION "Assessor", A.account_total_property_value as "Total Value",
    TaxLU.DESCRIPTION "Tax Status",

    CASE
        when A.assessor_id in ('ATHENDRATA', 'BRTHOMPSON', 'FTACIUNE', 'HPHILLIPS', 'JDCHU', 'JRYOUNG1', 'MHARTMANN', 'NCCHAN', 'RLEE5', 'SBERZINS') then 'Industrial'
        when A.assessor_id in ('ASMTDWA','ASMTEB', 'ASMTWS', 'BBROCKLEBANK', 'CCHICHAK', 'CYMAU', 'GJONES4', 'IYPAU', 'JTGREER', 'KHOUSTON', 'LGMORRISON', 'MMCKENZIE1', 'MWALZ', 'SKUANG', 'STBAZIN', 'TKNGUYEN1', 'ASHIELDS') then 'Single Residential'
        when A.assessor_id in ('BTANG', 'CMACMILLAN1', 'DGENCARELLI', 'EWU1', 'JWEHLER', 'LMDUNBAR', 'LWONG4', 'MGULOWSKA', 'RLEE1', 'SHAMMOUD', 'SLTURNER', 'YWANG') then 'Multi Residential'
        when A.assessor_id in ('CMULENGA', 'EPOPOVICS', 'GFULLER', 'IMCDERMOTT', 'JERMUBE', 'JNSTEVENSON', 'JPLEPINE', 'KBUCKRY', 'KDALMHJELL', 'NPHAM1', 'PGKERSEY', 'SMSAMPLE') then 'Special Purpose and Land'
        when A.assessor_id in ('ASMTHN', 'DCARSON', 'DLIDGREN', 'DMCCORD', 'EBORISENKO', 'HYAU1', 'MCTRIMBLE', 'RJTHARAKAN', 'TBJOHNSON1', 'VWONG1', 'WGIBBS', 'YYE', 'AVPETERS') then 'Office'
        when A.assessor_id in ('AKEAST', 'BLTHOMPSON', 'BNELSON1', 'JCRUJI', 'JWONG1', 'KGARDINER', 'KMHAUT', 'NTNGUYEN', 'RTLUCHAK', 'SGILL3', 'THEGER1', 'TJLUDLOW', 'ZRGEIB') then 'Retail'
        else 'Other'
    END as "Team"

    from REP_DBA.AB000_ACCOUNT A

    join REP_DBA.LU_ASMT_ASSIGN_ASSESSOR ALU
    on A.assessor_id = ALU.code

    -- Decode the Tax Status from the LU Table
    join rep_dba.LU_ASMT_ACCT_TAX_STATUS TaxLU
    on TaxLU.CODE = A.account_tax_status

    where A.rollyear = :rollyear
    and A.account_type = 'P'
    and A.account_status = 'AP'
    and A.account_total_property_value is not null
    -- ORDER BY account_total_property_value DESC -- not needed
  )
GROUP BY "Team", "Tax Status"
)
pivot
(
    min("Total Value")  -- here
    for ("Tax Status")
    in ('Exempt from Taxation' as "Exempt", 'Taxable' as "Taxable", 
        'Tax Agreement - Operator of a Public Utility' as "Tax Agreement", -- duplicate
        '"Assessed Person" Tax Agreement' as "Tax Agreement 1", -- changed alias, added 1
        'Grant in Place of Tax' as "Grant", 'Council Tax Cancellation or Refund' as "Council Cancel/Refund")
)

Cheers!!

Upvotes: 2

Related Questions