I am trying to create my first pivot query. I have two tables:
CREATE TABLE "SKIP"."ORDERS"
(
"CATG" VARCHAR2(30),
"PART_NR" VARCHAR2(30),
"PART_REV" VARCHAR2(30),
"CUST_ID" NUMBER,
"ORDER_DT" DATE )
;
CREATE TABLE "SKIP"."CUSTOMERS"
(
"STATE" VARCHAR2(30),
"CUST_ID" NUMBER )
;
I want to create a pivot table with years on top and part numbers on the side. I had a problem early on when I was trying this. It seems that you cannot use a function on the FOR clause, which prohibited me from using
PIVOT (COUNT(PART_NR) FOR extract (year from ORDERS.ORDER_DT) IN (
I don't remember the exact error it gave me, but I found a "possible" workaround with the nested query I have below
SELECT * FROM
(SELECT PART_NR, YR FROM (
(SELECT ORDERS.CATG || '-' || ORDERS.PART_NR || ORDERS.PART_REV AS PART_NR, extract (year from ORDERS.ORDER_DT) AS "YR"
FROM ORDERS, CUSTOMERS
where
CUSTOMERS.STATE = "VA"
and
ORDERS.CUST_ID = CUSTOMERS.CUST_ID
)
)
PIVOT (COUNT(PART_NR) FOR YR IN (
2002,
2003,
2004,
2005,
2006,
2007,
2008,
2009,
2010,
2011,
2012,
2013
)
)
)
ORDER BY PART_NR
But now I get an error: "YR": invalid identifier
I tested the two nested queries (starting with SELECT PART_NR, YR FROM ) and that did indeed get me the two columns PART_NR and YR, so the problem must be in the PIVOT.
Any ideas?