Can you code a subquery as part of a select statement in DB2? The query will work in SQL Server but gets an error...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.