|
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. > >
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.