ambiguity error when adding pivot to existing SQL oracle statement

Question

 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

Answer 1

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

fiddle