My query runs in Toad fine, but will not run in SQL Optimizer. Says ‘this type of SQL statement is not supported.’ Can anyone tell me what the ‘offender’ is? There is no log, no other information as to what caused the error.
WITH
eqseals(
svc_id,
seal_nr)
AS
(SELECT
svc_id,
Listagg(
TRIM(seal_nr),
‘,’)
within group (ORDER BY seal_nr)
AS seal_nr
FROM
intermodal.eq_seal
GROUP BY
svc_id
HAVING
svc_id IS NOT NULL)
SELECT
DISTINCT
el.last_evt_dt_tm,
el.eq_tp,
el.eq_init,
el.eq_nr,
(
CASE
WHEN el.eq_tp = ‘C’ THEN cont.cont_lgth / 12
WHEN el.eq_tp = ‘Z’ THEN chas.chas_lgth / 12
WHEN el.eq_tp = ‘T’ THEN trlr.trlr_lgth / 12
ELSE 0
END)
AS size,
s.load_empty_cd,
el.chas_init,
el.chas_nr,
coalesce(
nullif(
(
CASE
WHEN el.eq_tp = ‘C’ THEN TRIM(cont.dmge_ind)
WHEN el.eq_tp = ‘Z’ THEN TRIM(chas.dmge_ind)
WHEN el.eq_tp = ‘T’ THEN TRIM(trlr.dmge_ind)
ELSE ‘’
END),
‘’),
‘N’)
AS damage,
TRIM(holds.hold_reason_cd),
TRIM(s.cust_bol_nr),
TRIM(seals.seal_nr),
s.lading_wt,
s.wb_ser_nr,
tkr.tkr_cd,
s.haz_ship_ind,
TRIM(
coalesce(
nsorigterminal.term_nm,
nsorig.stn_nm))
AS terminal,
TRIM(orig.op_stn),
TRIM(nsorig.op_stn),
TRIM(nsdest1.op_stn),
TRIM(dest.op_stn),
TRIM(s.pickup_nr),
TRIM(
coalesce(
lot.lvl_1_val,
‘’)
|| ’ ’
|| coalesce(
lot.lvl_2_val,
‘’)
|| ’ ’
|| coalesce(
lot.lvl_3_val,
‘’))
AS lotloc,
s.cust_bol_dt,
s.wb_dt,
TRIM(tkr.tkr_nm),
el.last_aar_evt_cd,
el.car_init,
el.car_nr,
TRIM(s.wb_route),
TRIM(bo.ent_name),
TRIM(s.tofc_cofc_ind),
cust_chrg.last_free_dt_tm,
chrg_amt
AS storageamount,
TRIM(orig.stn_nm),
TRIM(nsorig.stn_nm),
TRIM(nsdest.stn_nm),
TRIM(dest.stn_nm),
TRIM(sv.vessel_nm)
AS vessel_nm,
TRIM(sv.voyage_nr)
AS voyage_nr,
sv.vessel_sail_dt,
TRIM(sv.book_nr)
AS book_nr,
sdd.dlvy_by_dt_tm
AS deliverby,
cust_chrg.chrg_days,
t1.term_nm
AS current_loc,
s.stcc_cd_5
|| s.stcc_cd_2
|| coalesce(
s.stcc_sub_cd,
0)
AS stcc,
c.CUST_NM
as shipper,
el.CURR_LOC_TP
as current_location_type,
receiver.CUST_NM
as receiver,
s.REQ_DLVY_ETA
as ETA,
s.OGATE_AV_ETA,
(
SELECT
min(nq.CREATE_DT_TM)
FROM
INTERMODAL.NTFY_QUEUE nq,
INTERMODAL.NS_EVT_LOG nel,
intermodal.shipment s1
WHERE
nel.SVC_ID = s1.SVC_ID AND
s1.SHIP_STAT = ‘A’ AND
nel.EVT_LOG_ID = nq.EVT_LOG_ID AND
nel.EVT_CD = ‘NOPA’ AND
nel.REASON_CD = ‘RMFC’ AND
nel.EQ_INIT = se.EQ_INIT AND
nel.EQ_NR = se.EQ_NR)
AS notification_time
FROM
intermodal.shipment s
LEFT OUTER JOIN intermodal.customer c ON s.ship_cust = c.cust_id
LEFT OUTER JOIN intermodal.customer receiver ON s.rcvr_cust = receiver.cust_id
LEFT OUTER JOIN intermodal.corp_cust cc ON c.corp_cust_id = cc.corp_cust_id
LEFT OUTER JOIN intermodal.corp_cust_dtl ccd
ON cc.corp_cust_id = ccd.corp_cust_id
LEFT OUTER JOIN intermodal.ship_eq se ON s.svc_id = se.svc_id
LEFT OUTER JOIN intermodal.eq_loc el
ON se.eq_init = el.eq_init AND
se.eq_nr = el.eq_nr
LEFT OUTER JOIN intermodal.eq_cont cont
ON el.eq_init = cont.cont_init AND
el.eq_nr = cont.cont_nr
LEFT OUTER JOIN intermodal.eq_chas chas
ON el.eq_init = chas.chas_init AND
el.eq_nr = chas.chas_nr
LEFT OUTER JOIN intermodal.eq_trlr trlr
ON el.eq_init = trlr.trlr_init AND
el.eq_nr = trlr.trlr_nr
LEFT OUTER JOIN intermodal.trucker tkr ON c.cust_id = tkr.cust_id
LEFT OUTER JOIN intermodal.hold_orders holds ON s.svc_id = holds.svc_id
LEFT OUTER JOIN intermodal.ship_dlvy_date sdd ON sdd.svc_id = s.svc_id
LEFT OUTER JOIN eqseals seals ON s.svc_id = seals.svc_id
LEFT OUTER JOIN intermodal.station_xrf nsorig ON s.onl_orig = nsorig.term_id
LEFT OUTER JOIN intermodal.station_xrf orig ON s.offl_orig = orig.term_id
LEFT OUTER JOIN intermodal.station_xrf nsdest ON s.onl_dest = nsdest.term_id
LEFT OUTER JOIN intermodal.station_xrf dest ON s.offl_dest = dest.term_id
LEFT OUTER JOIN intermodal.terminal nsorigterminal
ON s.onl_orig = nsorigterminal.stn_xrf_id
LEFT OUTER JOIN intermodal.terminal nsdestterminal
ON s.onl_dest = nsdestterminal.stn_xrf_id
LEFT OUTER JOIN intermodal.ship_vessel sv
ON s.svc_id = sv.svc_id AND
(sv.vessel_dir_cd = ‘I’)
LEFT OUTER JOIN intermodal.work_queue_log work_queue_log
ON s.svc_id = work_queue_log.svc_id
LEFT OUTER JOIN intermodal.terminal t ON work_queue_log.term_id = t.term_id
LEFT OUTER JOIN intermodal.terminal t1 ON el.term_id = t1.term_id
LEFT OUTER JOIN (SELECT
*
FROM
intermodal.ship_entity
WHERE
seg_tp IN (‘BN’)) bo
ON s.svc_id = bo.svc_id
LEFT OUTER JOIN intermodal.cust_chrg cust_chrg
ON chrg_tp = ‘STO’ AND
se.eq_init = cust_chrg.eq_init AND
se.eq_nr = cust_chrg.eq_nr AND
s.svc_id = cust_chrg.svc_id
LEFT OUTER JOIN intermodal.term_lot_area lot
ON el.lot_area_id = lot.lot_area_id
LEFT OUTER JOIN intermodal.station_xrf nsdest1
ON t1.STN_XRF_ID = nsdest1.term_id
WHERE
ccd.corp_primary_6 = ‘401122’ and
trim(
coalesce(
t1.term_nm,
‘UNKNOWN’)) in (‘AUSTELL’) AND
s.ship_stat = ‘A’ AND
SE.EQ_STAT = ‘A’ AND
el.last_aar_evt_cd IN (‘PLRM’) AND
el.curr_loc_tp IN (
‘CAR’,
‘RAILYARD’,
‘TRACK’)
with
ur;