Q
Problem solve Get help with specific problems with your technologies, process and projects.

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


This was last published in October 2003

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.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Yes, it does work in DB2. I am currently running a process now to test it, and it has not "errored out".

Thanks.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close