Toad World® Forums

Alias alignment


#1

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

#2

Sorry to say that development work on formatter was interrupted for some time due to other high priority work. We’ll be back soon and continue the rework of the alignment.

Thanks,
Andre