Derived table in DB2
Can you code a subquery as part of a select statement in DB2? The query will work in SQL Server but gets an error in DB2.
select B.CUSNO, B.Delays, B.ShpTons, ( SELECT Sum(CVIEW1.AC_WT/2000.0) from WAREHOUS.CORD_ORDER_VIEW CVIEW1 where CVIEW1.CUS_NO = B.CUSNO and CVIEW1.PROM_ACK_DT >= '2003-08-01' and CVIEW1.PROM_ACK_DT < '2003-09-01' and CVIEW1.ORD_STS_CD NOT IN ('C','I') ) as OrdTons FROM ...
If you've tried this query in DB2, and it failed, then presumably the answer is no (I don't actually have a copy of DB2 on which to test it). But do not despair; there is often more than one way to remove a feline's outer integument.
Based on the from-clause specification in the DB2 SQL Reference for Cross-Platform Development v1.1 manual, you can rewite your query using a derived table (which DB2 calls a "nested table expression").
In your original query, you have a correlation within the subquery between the CUS_NO column and the outer query's B.CUSNO column. This correlation can be rewritten as the JOIN condition.
select B.CUSNO, B.Delays, B.ShpTons , DT.AC_tons as OrdTons from ( select CUS_NO , Sum(AC_WT/2000.0) as AC_tons from WAREHOUS.CORD_ORDER_VIEW where PROM_ACK_DT >= '2003-08-01' and PROM_ACK_DT < '2003-09-01' and ORD_STS_CD NOT IN ('C','I') group by CUS_NO ) as DT inner join B on DT.CUS_NO = B.CUSNO