SELECT
*
FROM
(
SELECT
ordr.program,
ordr.order_id,
ordr.part_no,
ordr.order_no,
ordr.actual_start_date,
ser.serial_no,
oper.asgnd_machine_id,
oper.time_stamp,
oper.updt_userid,
oper.oper_status,
oper.oper_no ,
CASE
WHEN oper.oper_no IN ('1234') THEN 'paint_bike'
END AS oper_type
FROM
sfmfg.sfwid_order_desc ordr
LEFT JOIN sfmfg.sfwid_serial_desc ser
ON ordr.order_id = ser.order_id
LEFT JOIN sfmfg.sfwid_oper_desc oper
ON ordr.order_id = oper.order_id
AND oper.step_key = -1
AND ordr.program IN ('bike')
AND oper.oper_no IN ('1234')
WHERE
ordr.actual_start_date > TO_DATE('08/01/2023', 'MM/DD/YYYY')
AND ordr.part_no LIKE '123'
AND ser.serial_no LIKE '123')
and I want to add this pivot to the end of it.
PIVOT (
MAX(asgnd_machine_id) AS asgnd_machine_id,
MAX(time_stamp) AS time_stamp,
MAX(updt_userid) AS updt_userid,
MAX(oper_status) AS oper_status FOR oper_type IN ('bike_Cutting' AS bike_Cutting, 'bike_Prep' AS bike_Prep,
)
I am trying to get the pivot to work but I'm getting column ambiguously defined errors for my first select statement when I add alias such as this for the first select statement
SELECT
a1.program,
a1.order_id,
a1.customer_description,
a1.stiffener_type,
a1.mold_tool_no,
a1.part_no,
a1.order_no,
a1.actual_start_date,
a1.serial_no,
a1.asgnd_machine_id,
a1.time_stamp,
a1.updt_userid,
a1.oper_status,
a1.plan_title,
a1.oper_type
it gives me invalid identifier errors
When you PIVOT columns you need to combine the aliases from the FOR clause and the aggregation clause of the PIVOT:
SELECT a1.program,
a1.order_id,
-- a1.customer_description, -- These 4 columns are not in your query.
-- a1.stiffener_type,
-- a1.mold_tool_no,
--a1.plan_title,
a1.part_no,
a1.order_no,
a1.actual_start_date,
a1.serial_no,
a1.bike_cutting_asgnd_machine_id, -- combine the aliases for these columns
a1.bike_cutting_time_stamp,
a1.bike_cutting_updt_userid,
a1.bike_cutting_oper_status,
a1.bike_prep_asgnd_machine_id,
a1.bike_prep_time_stamp,
a1.bike_prep_updt_userid,
a1.bike_prep_oper_status
FROM (
SELECT ordr.program,
ordr.order_id,
ordr.part_no,
ordr.order_no,
ordr.actual_start_date,
ser.serial_no,
oper.oper_no,
-- pivot
oper.asgnd_machine_id,
oper.time_stamp,
oper.updt_userid,
oper.oper_status,
CASE
WHEN oper.oper_no IN ('1234') THEN 'paint_bike'
WHEN oper.oper_no IN ('5678') THEN 'bike_Cutting'
WHEN oper.oper_no IN ('9999') THEN 'bike_Prep'
END AS oper_type
FROM /*sfmfg.*/sfwid_order_desc ordr
LEFT JOIN /*sfmfg.*/sfwid_serial_desc ser
ON ordr.order_id = ser.order_id
LEFT JOIN /*sfmfg.*/sfwid_oper_desc oper
ON ordr.order_id = oper.order_id
AND oper.step_key = -1
AND ordr.program IN ('bike')
AND oper.oper_no IN ('1234')
WHERE ordr.actual_start_date > TO_DATE('08/01/2023', 'MM/DD/YYYY')
AND ordr.part_no LIKE '123'
AND ser.serial_no LIKE '123'
)
PIVOT (
MAX(asgnd_machine_id) AS asgnd_machine_id,
MAX(time_stamp) AS time_stamp,
MAX(updt_userid) AS updt_userid,
MAX(oper_status) AS oper_status
FOR oper_type IN (
'bike_Cutting' AS bike_Cutting,
'bike_Prep' AS bike_Prep
)
) a1