Скачать или смотреть ambiguity error when adding pivot to existing SQL oracle statement

Hello everyone! I hope this video has helped solve your questions and issues. This video is shared because a solution has been found for the question/problem. I create videos for questions that have solutions. If you have any other issues, feel free to reach out to me on Instagram:   / ky.emrah  

Below, you can find the text related to the question/problem. In the video, the question will be presented first, followed by the answers. If the video moves too fast, feel free to pause and review the answers. If you need more detailed information, you can find the necessary sources and links at the bottom of this description. I hope this video has been helpful, and even if it doesn't directly solve your problem, it will guide you to the source of the solution. I'd appreciate it if you like the video and subscribe to my channel!ambiguity error when adding pivot to existing SQL oracle statement

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

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

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

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


Tags: oracle,pivot,ambiguitySource of the question:
https://stackoverflow.com/questions/7...

Question and source license information:
https://meta.stackexchange.com/help/l...
https://stackoverflow.com/