I see several comments/inquiries about alias alignment, but no solutions or the solutions are so old and outdated as to be irrelevant.
Points for consistency, but consistently bad is not good. This is an unacceptable mess. How do I fix it?
SELECT DISTINCT
CASE P_EVENT_NAME
WHEN k_LoadTendered_event THEN k_LoadTendered_event_id
WHEN k_LoadTenderAccepted_event THEN k_LoadTenderAccepted_event_id
WHEN k_LoadStopAppointment_event THEN k_LoadStopAppointment_event_id
WHEN k_LoadStopConfirm_event THEN k_LoadStopConfirm_event_id
WHEN k_LoadStopDelivered_event THEN k_LoadStopDelivered_event_id
END
event_id,
SUBSTR(REGEXP_SUBSTR(lldt.shpm_num, '[^-]+', 1), 1, 20)
po_no,
md.transfer_num,
NULL
split_id,
CASE
WHEN llt.cdty_cd = 'CPU' THEN 'CPU'
WHEN poh.freight_terms_code = 'FO' THEN 'FOB'
WHEN poh.freight_terms_code = 'FP' THEN 'PPD'
END
freight_terms,
SUBSTR(ct.carr_cd, 1, 4)
carrier_scac,
ct.name
carrier_name,
llt.srvc_cd
trans_mode,
CASE
WHEN te.event_name = k_LoadStopConfirm_event THEN llt.trlr_num
ELSE NULL
END
trailer_num,
NULL
container_num,
CASE
WHEN P_EVENT_NAME = k_LoadStopDelivered_event THEN llt.last_shpg_loc_cd
ELSE NULL
END
dc_num,
CASE
WHEN P_EVENT_NAME IN (k_LoadStopAppointment_event, k_LoadStopConfirm_event) THEN
llt.frst_shpg_loc_name
WHEN P_EVENT_NAME = k_LoadStopDelivered_event THEN
llt.last_shpg_loc_name
ELSE
NULL
END
event_location,
CASE
WHEN P_EVENT_NAME IN (k_LoadStopAppointment_event, k_LoadStopConfirm_event) THEN
llt.frst_cty_name
WHEN P_EVENT_NAME = k_LoadStopDelivered_event THEN
llt.last_cty_name
ELSE
NULL
END
event_city,
CASE
WHEN P_EVENT_NAME IN (k_LoadStopAppointment_event, k_LoadStopConfirm_event) THEN
llt.frst_sta_cd
WHEN P_EVENT_NAME = k_LoadStopDelivered_event THEN
llt.last_sta_cd
ELSE
NULL
END
event_state,
CASE
WHEN P_EVENT_NAME IN (k_LoadStopAppointment_event, k_LoadStopConfirm_event) THEN
llt.frst_ctry_cd
WHEN P_EVENT_NAME = k_LoadStopDelivered_event THEN
llt.last_ctry_cd
ELSE
NULL
END
event_country,
TO_DATE(REPLACE(xml.event_occurred, 'T'), 'yyyy-mm-dd hh24:mi:ss')
date1,
CASE
WHEN P_EVENT_NAME NOT IN (k_LoadTendered_event, k_LoadTenderAccepted_event) THEN
TO_DATE(REPLACE(xml.event_reported, 'T'), 'yyyy-mm-dd hh24:mi:ss')
END
date2,
NULL
date3,
NULL
date4,
NULL
date5,
CASE WHEN llt.cdty_cd = 'CPU' THEN 'CPU' ELSE NULL END
text1,
CASE WHEN xml.backhaul_flag = 'BHI' THEN 'BH' ELSE NULL END
text2,
CASE WHEN get_carrier_type(llt.carr_cd) = 'IM' THEN 'IMDL' ELSE NULL END
text3,
NULL
text4,
NULL
text5,
CASE
WHEN P_EVENT_NAME IN (k_LoadStopConfirm_event, k_LoadStopDelivered_event) THEN
CASE
WHEN cdty_cd = '*DFT' THEN xml.picked_pieces
ELSE xml.dropped_pieces
END
ELSE
NULL
END
qty1,
NULL
qty2,
NULL
qty3,
NULL
qty4,
NULL
qty5,
-- the following are just filler to fill out the record structure
NULL
create_date,
NULL
create_user,
NULL
last_update_date,
NULL
last_update_user