Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2017
    Posts
    1

    Unhappy Answered: DECFLOAT Error caused by where clause Subquery

    Thanks for reading!

    I have a piece of SQL (Snippet 1) that looks up some damage information, using Equipment Numbers found in a subquery. If I place a 'simple' subquery in there (Snippet 2), it works fine with no errors. However, my slightly more complex subquery is returning a DECFLOAT error and I've isolated it to two lines of SQL (Snippet 3)

    Snippet 1: The working outter query
    Code:
    Select  dam.eq_NR, comp.comp_dscr, dam.loc_dscr, aar.aar_description, dam.quantity, (dam.dmge_size concat  ' ' concat  dam.size_display_tp) as dmg_size_c
    
     from INTERMODAL.DAMAGE_DTL_2_V dam
    Inner  join INTERMODAL.DAMAGE_COMPONENT comp on dam.cat_cd = comp.Job_code
    Inner Join INTERMODAL.AAR_WHY_MADE_CODES aar on dam.aar_reason_cd = aar.aar_why_made_cd
    
    Where dam.EQ_NR in  (Subquery - See Snippet 2 and 3)
    Snippet 2: Simple subquery that returns the exact same data set as Snippet 3
    Code:
    select distinct e.EQ_NR
    From INTERMODAL.SHIP_EQ e
    Where e.EQ_NR in ('279111','694523','390874','131090')
    Snippet 3: The Broken Subquery
    Code:
    select distinct e.EQ_NR
    from INTERMODAL.SHIPMENT s
    inner join INTERMODAL.SHIP_EQ e on e.SVC_ID = s.SVC_ID
    inner join (select stn_nm, term_id
    			from INTERMODAL.STATION_XRF
    			where STN_NM = 'BALTIMORE PIERS')  d on s.ONL_DEST = d.TERM_ID
    left join (select * from INTERMODAL.NS_EVT_LOG evt where evt.evt_cd = 'ICHD') ICHD on ICHD.svc_id = s.SVC_ID
    left join (select * from INTERMODAL.NS_EVT_LOG evt where evt.evt_cd = 'RMFC') RMFC on RMFC.svc_id = s.SVC_ID
    left join INTERMODAL.SHIP_VESSEL v on s.SVC_ID = v.SVC_ID
    
    where 
    v.VESSEL_DIR_CD = 'I'
    and e.EQ_TP <> 'Z'
    
    --Removing these two lines allows this subquery to work
    and ICHD.evt_dt_tm is null
    and RMFC.evt_dt_tm is not null
    If I remove these two components, the subquery works fine (except it's returning much more data than I need)
    and ICHD.evt_dt_tm is null
    and RMFC.evt_dt_tm is not null

    Removing their associate joins has no impact.

    I'm truly at a loss at what could be causing this, any assistance is greatly appreciated.

  2. Best Answer
    Posted by DNance

    "One thing that might be causing this is putting predicates in the wrong place and you might as well write the outer joins more efficiently as well. If you are using explicit join syntax, then put all predicates for that table in the ON clause for Outer joins it will change what data you see. For the outer joins, putting a predicate into the WHERE clause, the database performs the OUTER join, saves the result set in memory, then performs an inner join to that resultset to satisfy the predicates in the WHERE.

    Code:
    select distinct e.EQ_NR
    from INTERMODAL.SHIPMENT s
    inner join INTERMODAL.SHIP_EQ e
        on e.SVC_ID = s.SVC_ID
       and e.EQ_TP <> 'Z'
    inner join INTERMODAL.STATION_XRF d
        on s.ONL_DEST = d.TERM_ID
       and d.STN_NM = 'BALTIMORE PIERS'
    left join INTERMODAL.NS_EVT_LOG ICHD
        on ICHD.svc_id = s.SVC_ID
       and ICHD.evt_cd = 'ICHD'
       and ICHD.evt_dt_tm is null
    left join INTERMODAL.NS_EVT_LOG RMFC
        on RMFC.svc_id = s.SVC_ID
       and RMFC.evt_cd = 'RMFC'
       and RMFC.evt_dt_tm is not null
    left join INTERMODAL.SHIP_VESSEL v
        on s.SVC_ID = v.SVC_ID
       and v.VESSEL_DIR_CD = 'I'
    Also, do you need two representations of the event log table?
    Code:
    select distinct e.EQ_NR
    from INTERMODAL.SHIPMENT s
    inner join INTERMODAL.SHIP_EQ e
        on e.SVC_ID = s.SVC_ID
       and e.EQ_TP <> 'Z'
    inner join INTERMODAL.STATION_XRF d
        on s.ONL_DEST = d.TERM_ID
       and d.STN_NM = 'BALTIMORE PIERS'
    left join INTERMODAL.NS_EVT_LOG ICHD
        on ICHD.svc_id = s.SVC_ID
       and ICHD.evt_cd in ('ICHD','RMFC')
       and ((ICHD.evt_cd = 'ICHD'
         and ICHD.evt_dt_tm is null)
         or (ICHD.evt_cd = 'RMFC'
         and ICHD.evt_dt_tm is not null))
    left join INTERMODAL.SHIP_VESSEL v
        on s.SVC_ID = v.SVC_ID
       and v.VESSEL_DIR_CD = 'I'
    Dave"


  3. #2
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    One thing that might be causing this is putting predicates in the wrong place and you might as well write the outer joins more efficiently as well. If you are using explicit join syntax, then put all predicates for that table in the ON clause for Outer joins it will change what data you see. For the outer joins, putting a predicate into the WHERE clause, the database performs the OUTER join, saves the result set in memory, then performs an inner join to that resultset to satisfy the predicates in the WHERE.

    Code:
    select distinct e.EQ_NR
    from INTERMODAL.SHIPMENT s
    inner join INTERMODAL.SHIP_EQ e
        on e.SVC_ID = s.SVC_ID
       and e.EQ_TP <> 'Z'
    inner join INTERMODAL.STATION_XRF d
        on s.ONL_DEST = d.TERM_ID
       and d.STN_NM = 'BALTIMORE PIERS'
    left join INTERMODAL.NS_EVT_LOG ICHD
        on ICHD.svc_id = s.SVC_ID
       and ICHD.evt_cd = 'ICHD'
       and ICHD.evt_dt_tm is null
    left join INTERMODAL.NS_EVT_LOG RMFC
        on RMFC.svc_id = s.SVC_ID
       and RMFC.evt_cd = 'RMFC'
       and RMFC.evt_dt_tm is not null
    left join INTERMODAL.SHIP_VESSEL v
        on s.SVC_ID = v.SVC_ID
       and v.VESSEL_DIR_CD = 'I'
    Also, do you need two representations of the event log table?
    Code:
    select distinct e.EQ_NR
    from INTERMODAL.SHIPMENT s
    inner join INTERMODAL.SHIP_EQ e
        on e.SVC_ID = s.SVC_ID
       and e.EQ_TP <> 'Z'
    inner join INTERMODAL.STATION_XRF d
        on s.ONL_DEST = d.TERM_ID
       and d.STN_NM = 'BALTIMORE PIERS'
    left join INTERMODAL.NS_EVT_LOG ICHD
        on ICHD.svc_id = s.SVC_ID
       and ICHD.evt_cd in ('ICHD','RMFC')
       and ((ICHD.evt_cd = 'ICHD'
         and ICHD.evt_dt_tm is null)
         or (ICHD.evt_cd = 'RMFC'
         and ICHD.evt_dt_tm is not null))
    left join INTERMODAL.SHIP_VESSEL v
        on s.SVC_ID = v.SVC_ID
       and v.VESSEL_DIR_CD = 'I'
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •