Início / user-27573469

fleshwound's questions

Martin Hope
fleshwound
Asked: 2024-10-01 23:32:44 +0800 CST
  • 5
 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')

e quero adicionar esse pivô no final dele.

    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,
       )

Estou tentando fazer o pivô funcionar, mas estou recebendo erros de definição ambígua de coluna para minha primeira instrução select quando adiciono um alias como este para a primeira instrução select

 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

me dá erros de identificador inválido

oracle