Invalid identifier error when pivot in oracle sql

user_16626008 0
Viewed 82

I am trying to pivot sales (ie quantity*price) from order_tbl, but getting

"ORA-00904: "Sales" invalid identifiers".

can anyone please help what wrong is wrong with the below sql?

SELECT order_pay, product_id, (quantity * price) as sales
          FROM order_tbl
       PIVOT (SUM (sales) FOR order_pay IN ('01-MAY-2015', '02-MAY-2015'))

Script to create table and insert data is as below.

CREATE TABLE ORDER_TBL
(
  ORDER_PAY   DATE,
  ORDER_ID    VARCHAR2(10 BYTE),
  PRODUCT_ID  VARCHAR2(10 BYTE),
  QUANTITY    NUMBER(5),
  PRICE       NUMBER(5)
)

Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD1', 'PROD1', 5, 5);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD2', 'PROD2', 2, 10);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD3', 'PROD3', 10, 25);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD4', 'PROD1', 20, 5);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD5', 'PROD3', 5, 25);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD7', 'PROD1', 2, 5);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD8', 'PROD5', 1, 50);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD9', 'PROD6', 2, 50);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD10', 'PROD2', 4, 10);
Insert into ORDER_TBL
   (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
 Values
   (TO_DATE('5/2/2015', 'MM/DD/YYYY'), 'ORD6', 'PROD4', 6, 20);
COMMIT;
pivot oracle sql
1 Answers

Column aliases cannot be used in the same level of query, so sales isn't recognised as an identifier in the pivot clause. it also won't recognise price or quantity with your current query as those aren't in the projection being pivoted, because the calculates sales is there instead - which seems a bit contradictory.

As mentioned in comments, you could change the query to do the aggregation directly in the pivot clause (so you don't calculate sales as a separate values, and get the two base columns in the initial select list), but that doesn't work either, unless you use an inline view:

SELECT *
FROM (
  SELECT order_pay, product_id, quantity, price
  FROM order_tbl
)
PIVOT (SUM (quantity * price) FOR order_pay IN ('01-MAY-2015', '02-MAY-2015'))

db<>fiddle.

If you particularly want to be able to reference sales in the pivot clause then you do that sales calculation in an inline view instead:

SELECT *
FROM (
  SELECT order_pay, product_id, (quantity * price) as sales
  FROM order_tbl
)
PIVOT (SUM (sales) FOR order_pay IN ('01-MAY-2015', '02-MAY-2015'))

But '01-MAY-2015' is a string, not a date, so you are relying on implicit conversion and NLS settings; it would be better to use date literals:

SELECT *
FROM (
  SELECT order_pay, product_id, (quantity * price) as sales
  FROM order_tbl
)
PIVOT (SUM (sales) FOR order_pay IN (DATE '2015-05-01', DATE '2015-05-02'))

And of course you can give the pivoted columns more friendly aliases within the pivot clause too, and order the results, to get something like:

PRODUCT_ID May01 May02
PROD1 125 10
PROD2 20 40
PROD3 250 125
PROD4 null 120
PROD5 null 50
PROD6 null 100

db<>fiddle

user_2663040
Related