Need to include a new detai; table

1

the data that is created has summary totals like Invoice Amount. Now we don't want invamt but the invamt without the freight charges. Each detail row has a part#, qty, and sale price. We need to join det_table on the otord# = idord#.

But also to sum the det_table, per order, the idprt# must not start with 'FRT' thats how we know freight. I am not sure what sort of join i think left outer. I then need to include in the first line below, the (qty* sale_price as lintot where idprt# does not start with 'FRT') but then also sum this lintot for the order#.

CREATE VIEW CLSPAYTP AS SELECT OTCOM#, OTORD#, OTTRND, OTTRT, INVAMT,
IHORG$, IHORD$, IHINV#,
OTUSRN, OTTRNC, IHPTTC, IHSLR#, IHORDT,
IHDOCD, RFCAT, RFSLC,
RFSQ2, RFDTA
FROM AZLIB.CLSPAYTPP
WHERE
OTTRNC='001'
UNION
SELECT
OTCOM#, OTORD#, OTTRND, OTTRT, INV
INVAMT, IHORG$, IHORD$,IHINV#, OTUSRN, OTTRNC, IHPTTC,
IHSLR#, IHORDT,

IHDOCD, RFCAT, RFSLC, RFSQ2, RFDTA FROM                               
AZLIB.CLSPAYTPP OT1         
WHERE OTTRT = (SELECT MIN(OTTRT)                                        
               FROM AZLIB.CLSPAYTPP OT2              

  WHERE OT2.OTTRNC<>'001' AND OT1.OTORD#= OT2.OTORD#                         
                GROUP BY OT2.OTORD#)