Programming

createtablea1as 
selecta.tps_Res_nb,
b.tkt_prod_cd, 
b.tkt_prod_typ_nm,
b.prod_intrnl_ds,
b.tkt_prod_typ_nm AS TKT_ENTL_NM,
casewhen b.tkt_prod_nm isnotnullthen b.tkt_prod_nm
when b.tkt_prod_nm isnulland b.prod_intrnl_ds isnotnullthen b.prod_intrnl_ds
else b.tkt_prod_nm 
endas tkt_prod_nm
FromWDW_PRARVL_GSTA
innerjoin crmmprod.WTRIP_ACM_TKT b
ona.tps_res_nb = b.ord_nb
 
createtableb1as 
selecta.*,
b.*
froma1a
leftjoinTKT_ATTx b
ona.TKT_PROD_CD = b.code
 
createtabletest2nologgingas 
selecta.*
from(select b.*,
ROW_NUMBER ()OVER(PARTITIONBY b.tps_Res_nb ORDERBY b.daycount descnullslast, 
b.adult, b.dayclass, b.tkt_prod_typ_nm, b.tkt_prod_nm, b.prod_intrnl_ds)AS ROW_RN fromb1 b)a

SELECT a.tps_res_nb,
       b.tkt_prod_cd,
       b.tkt_prod_typ_nm,
       b.prod_intrnl_ds,
       b.tkt_prod_typ_nm AS tkt_entl_nm,
       NVL (b.tkt_prod_nm, b.prod_intrnl_ds) AS tkt_prod_nm,
       c.*,
       ROW_NUMBER () OVER (PARTITION BY a.tps_res_nb
                               ORDER BY c.daycount DESC NULLS LAST,
                                        c.adult,
                                        c.dayclass,
                                        b.tkt_prod_typ_nm,
                                        NVL (b.tkt_prod_nm, b.prod_intrnl_ds),
                                        b.prod_intrnl_ds
                          ) AS row_rn
  FROM wdw_prarvl_gsta
 INNER JOIN crmmprod.wtrip_acm_tkt b
    ON (a.tps_res_nb = b.ord_nb)
 LEFT OUTER JOIN tkt_attx c
    ON (b.tkt_prod_cd = c.code)