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