Toad World® Forums

SQL Optimizer 'this type of SQL statement is not supported'


#1

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;


#2

It could be that the SQL Optimizer doesn’t like the table expression (WITH…), but here is one without it that gets the same generic ‘this type of SQL statement is not supported’ error.

SELECT el.last_evt_dt_tm
, se.eq_tp
, se.eq_init
, se.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 ‘’ END) AS size
, s.load_empty_cd
, el.chas_init
, el.chas_nr
, (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) 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 tkr
, s.haz_ship_ind
, t1.term_nm
, TRIM(orig.op_stn)
, TRIM(nsorig1.op_stn)
, TRIM(nsdest.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(gr.driver_first_nm) || ’ ’ || TRIM(gr.driver_last_nm) AS drivername,
sdd.dlvy_by_dt_tm AS deliverby,
t1.term_nm AS current_loc,
s.stcc_cd_5 || s.stcc_cd_2 || Coalesce(s.stcc_sub_cd, 0) AS stcc,
s.req_dlvy_eta AS req_dlvy_eta,
c.CUST_NM AS shipper,
el.CURR_LOC_TP AS current_location_type,
receiver.CUST_NM AS receiver,
s.OGATE_AV_ETA
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.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 (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) 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 intermodal.station_xrf nsorig1 ON t1.stn_xrf_id = nsorig1.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 (SELECT n.svc_id, n.tkr_cd, g.driver_first_nm, g.driver_last_nm
FROM intermodal.gate_receipt_2 g JOIN intermodal.ns_evt_log n ON g.evt_log_id = n.evt_log_id
WHERE n.evt_cd = ‘ICHR’) gr
ON s.svc_id = gr.svc_id
LEFT OUTER JOIN intermodal.trucker tkr ON gr.tkr_cd = tkr.tkr_cd
WHERE s.ship_stat = ‘A’ AND SE.EQ_STAT = ‘A’ AND el.last_aar_evt_cd IN (‘LDFC’) AND el.curr_loc_tp IN (‘CAR’) AND
ccd.corp_primary_6 =
‘401122’ AND trim(coalesce(t1.term_nm, ‘UNKNOWN’)) IN (‘AUSTELL’) AND se.eq_stat = ‘A’
WITH UR;


#3

I have to admit I did not know the answer. I asked our SQL Optimizer team and they responded:
The current version of SQL Optimizer does not support LISTAGG(…) WITHIN GROUP(ORDER BY …) syntax.
This syntax inside SQL statement cause the error message. They have created an issue for future follow up: SOFUL-148.


#4

Jeff, Thanks so much. After removing the listagg clause it runs fine.