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