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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

SearchFinancialApplications

Close