RE: SQL Tip

Just some more info...

This is called a "Common Table Expression (CTE)"

Your example works the same as the following "nested table expression":

Select Batch#,Div2 
from (Select Batch#,Div2 
      from OrsBatch 
      group by batch#, div2
     ) as Batch_Sum 
group by Batch#,Div2 having  
count(*) > 1     


I think the Common Table Expression is cleaner than the nested table expression.

Here's another cool (IMHO) example of using CTEs:

with Schedule as                                                        
        (select pimodl, piiall, CvtToDate(dochdt) as ShipDate           
          from meldbf/mppriv, meldbf/tpdocu                             
          where pimodl = domelc and CvtToDate(dochdt) >= CURRENT_DATE   
            and pimodl in (select mdmelc from meldbf/mpmodl             
                           where CvtToDate(mdendd) > CURRENT_DATE)      
          group by pimodl, piiall, dochdt                               
        ),                                                              
     EndingBalances as                                                  
        (select pimodl, piiall, ShipDate,                               
              cast((piiall - (select sum(b.doqty)                       
                         from meldbf/tpdocu b                           
                         where b.domelc = Schedule.pimodl               
                         and CvtToDate(b.dochdt) between CURRENT_DATE   
                                                 and Schedule.ShipDate  
                        )                                               
                   ) as dec(9,1))  as EndQty                            
         from Schedule                                                  
        ),                                                              
   FirstDay as                                                          
        (select pimodl, min(ShipDate) as RunOutDate                          
           from EndingBalances                                               
           where EndQty < 0                                                  
           group by pimodl, piiall                                           
        )                                                                    
select  distinct                                                             
   A.pimodl,C.mdcstm, B.RunOutDate,                                          
    case                                                                     
      when B.RunOutDate between CURRENT_DATE and (CURRENT_DATE + 7 Days)     
         then '*'                                                            
      else ' '                                                               
    end as Cur,                                                              
   A.EndQty, substr(A.pimodl,1,1) as Type                                    
from   meldbf/mpmodl C, FirstDay B, EndingBalances A                         
where  a.pimodl = b.pimodl and a.ShipDate = b.RunOutDate                     
  and  c.mdmelc = a.pimodl and CvtToDate(mdendd) > CURRENT_DATE              
  and  c.mdccde <> 'L10'                                                     


The above looks at current inventory (mppriv) and currently scheduled orders 
(tpdocu) to determine when we'll run out of inventory on-hand.  If the 
RunOutDate is 1 week or less away, if flags the line so that the production 
manager can double check that the part is scheduled for production.


HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeffrey Young
> Sent: Wednesday, June 22, 2005 8:58 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: SQL Tip
> 
> 
> I came across this neat trick using SQL recently and decided 
> to share with any of you that may not be famiilar with it.
> Using the WITH statement as the start of your SQL, you can 
> create a table in memory that can be used in a full select 
> statement, thus avoiding the need to create an intermediary table.
> For example, if you have a table with multiple records for a 
> given key, and wanted to know if there were any duplicates in 
> the table.
> In the past, you would have to create a table that summarized 
> the first table, then a query to test if any summary groups 
> had more than one record.
> The example below shows how to do this in one statement.
>  
> With Batch_Sum as (Select Batch#,Div2 from OrsBatch group by batch#, 
> div2) Select Batch#,Div2 from Batch_Sum group by Batch#,Div2 having  
> count(*) > 1                                                         
>  
> Hope this helps someone.
>  
> 
> 
> Jeff Young 
> Sr. Programmer Analyst
> Dynax Solutions, Inc.
> IBM -e(logo) server Certified Systems Exper - iSeries 
> Technical Solutions V5R2 
> IBM  Certified Specialist- e(logo) server i5Series Technical 
> Solutions Designer V5R3
> 
> IBM  Certified Specialist- e(logo)server i5Series Technical 
> Solutions Implementer V5R3
> 
>   
>  
> 
> 
> 
> 
> 
> 
> 
>               
> ---------------------------------
> Yahoo! Mail
>  Stay connected, organized, and protected. Take the tour
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>