CODEWITHSUNDEEP
sqloracle-databasepivot-table
Abhishek Malik
Abhishek Malik

Reputation: 577

Invalid identifier in pivot table

Here is my query:

SELECT NVL(REVENUE,0), OUTNO, MONTH_NAME FROM 
        (                                                            
         SELECT ROUND((RETURNDATE-STARTDATE)*DAILYRATE) AS REVENUE, 
                OUTNO, 
                EXTRACT(MONTH FROM RETURNDATE)AS MONTH_NAME
         FROM   RAGREEMENT LEFT JOIN VEHICLE ON 
                RAGREEMENT.LICENSENO=VEHICLE.LICENSENO
                AND EXTRACT(YEAR FROM RETURNDATE)=EXTRACT(YEAR FROM SYSDATE)-1
        )
PIVOT (
   SUM(REVENUE)
   FOR OUTNO IN (1,2,3,4,5,6,-1 AS TOTAL)
  )
ORDER BY MONTH_NAME;

and here is the error

SELECT NVL(REVENUE,0), OUTNO, MONTH_NAME FROM
                   *
ERROR at line 1:
ORA-00904: "OUTNO": invalid identifier

I fail to understand why this happens when SELECT * works perfectly

Upvotes: 1

Views: 578

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

What comes out from a pivot is a whole new set of columns names, and for each one of those new columns, you will need an NVL() or COALESCE(). This is because a completely new "matrix" is formed and many positions in this can be null. You cannot overcome this by using NVL() in the inner subquery.

Assuming you want months as columns your query might look more like this:

SELECT
   OUTNO
 , NVL('M1', 0)
 , NVL('M2', 0)
 , NVL('M3', 0)
 , NVL('M4', 0)
 , NVL('M5', 0)
 , NVL('M6', 0)
 , NVL('M7', 0)
 , NVL('M8', 0)
 , NVL('M9', 0)
 , NVL('M10', 0)
 , NVL('M11', 0)
 , NVL('M12', 0)
FROM (
     SELECT
            ROUND((RETURNDATE - STARTDATE) * DAILYRATE) AS REVENUE
          , OUTNO
          , 'M' || EXTRACT(MONTH FROM RETURNDATE) AS MONTH_NAME
     FROM RAGREEMENT
     LEFT JOIN VEHICLE ON RAGREEMENT.LICENSENO = VEHICLE.LICENSENO
         AND EXTRACT(YEAR FROM RETURNDATE) = EXTRACT(YEAR FROM SYSDATE) - 1
     )
PIVOT(
      SUM(REVENUE)
      FOR MONTH_NAME IN ('M1','M2','M3','M4','M5','M6','M7','M8','M9','M10','M11','M12')
     )
ORDER BY OUTNO;

This line produces the new columns:

      FOR MONTH_NAME IN ('M1','M2','M3','M4','M5','M6','M7','M8','M9','M10','M11','M12')

and it is each one of these you will need to "fix" for nulls in the select clause.

To put OUTNO values into columns requires a similar pattern but you need to know what the distinct set of values will be from that originating column. This MIGHT be 1,2,3,4,5,6,-1 but I wasn't certain.

nb: I prefixed 'M' in columns headings as many systems object to numbers as heading names.

Upvotes: 1

Related Questions