Help with SQL statement Performance -- MIDRANGE-L

This is an extension of my previous topic of SQL View vs CTE performance. Seems the performance is related to a specific table and not the method.

I've done quite a bit of trial and error to isolate my problem. Now I don't know to fix it (and index advisor isn't showing anything).

I've tried two different statements with the same result.
If I remove the EXCEPTION JOIN to table OEBOXCBX, my queries go from 20+ seconds down to 1.2 seconds.

The table OEBOXCBX contains 10 million records. There are existing LF for OEBOXCBX with keys beginning with BXCOM#, BXORD#, BXREL#, + additional fields (BXBOX#, BXPRT#). But those 3 keys alone are not unique.

VE shows a Hash Table built over this file consuming 20 seconds.
The list of key columns are:
BXORD#
BXCOM#
Cast (BXREL# as DEC(5,0))
** This doesn't make sense because BXREL# is defined using a field reference file (DDS) as 5 0

If I generate the SQL definition, the field is described like this:
BXREL# NUMERIC(5, 0) NOT NULL DEFAULT 0 ,

Instead of DEC(5, 0)

Any suggestions would be appreciated...

My SQL statements for reference:

First One:
With PicksByLoc as (
select odwhs#, odcom#, odprt#, pkloc1, pkloc2, pkloc3, sum(odctt#) as on_pick from oeordlod
join oelocnpk on pkcom#=odcom# and pkord#=odord# and pkrel#=odrel# and pkprt#=odprt# and pkorg#=odorg#
EXCEPTION JOIN OEBOXCBX ON BXCOM#=ODCOM# AND BXORD#=ODORD# AND BXREL#=ODREL#
where odctt# > 0
group by odwhs#, odcom#, odprt#, pkloc1, pkloc2, pkloc3
)
select odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3, on_pick, irqoh# from PicksByLoc
join icprtmia on iacom#=odcom# and iaprt#=odprt#
join icbldtir on irwhs#=odwhs# and ircom#=odcom# and irloc1=pkloc1 and irloc2=pkloc2 and irloc3=pkloc3 and irprt#=odprt#
where on_pick > irqoh#
;

Second Try:
with pickdetail as (
select odwhs#, odcom#, odord#, odrel#, odprt#, odctt#, pkloc1, pkloc2, pkloc3 from oeordlod
join oelocnpk on pkcom#=odcom# and pkord#=odord# and pkrel#=odrel# and pkprt#=odprt# and pkorg#=odorg#
where odctt# > 0)

select odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3, sum(odctt#) as on_pick, max(irqoh#) as on_hand from Pickdetail
join icprtmia on iacom#=odcom# and iaprt#=odprt#
join icbldtir on irwhs#=odwhs# and ircom#=odcom# and irloc1=pkloc1 and irloc2=pkloc2 and irloc3=pkloc3 and irprt#=odprt#
EXCEPTION JOIN OEBOXCBX ON BXCOM#=ODCOM# AND BXORD#=ODORD# AND BXREL#=ODREL#
where odctt# > irqoh#
group by odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3
;
[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>