RE: SQL Question

Ok, how about this:

With Earliest_Correction as
(select 
  Cust#
  PO#
  min(Correction_Date_YYMMDD * 1000000 + Correction_Time HHMMSS)
  Old/New Code (O = Original)
  Original Cancel Date YYMMDD
 from
  order_correction
 group by
  Cust#
  PO#
  Old/New Code (O = Original)
  Original Cancel Date YYMMDD
)

Select
 a.cust,
 a.PO,
 coalesce(c.correction_date, a.current_cancel_date),
 b.div,
 b.brand,
 b.subbrand
From
 Order_Header a join
 Order_detail b on
  a.cust = b.cust and
  a.PO = b.PO left outer join
 Earliest_Correction c on
  a.cust = c.cust and
  a.po = c.po
Where
 b.div = 'SOME DIv'and
 b.sub-brand = 'Stuff' and
 a.cust in ('cust1','cust2') and
 coalesce(c.correction_date, a.current_cancel_date) >= start_date and
 coalesce(c.correction_date, a.current_cancel_date) <= end_date

This SQL uses something called a Common Table Expression to turn the
results of a query into a sort of virtual table (earliest_correction),
which you can then join to your other tables

Chris



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Monday, December 04, 2006 10:25 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Question

Clarification.
I need to select the record from the Order Corrections file with the
earliest correction date / time for the Cust# & PO#.

Thanks,
 
Jeff Young 
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
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 


  
 





----- Original Message ----
From: Jeff Young <cooljeff913@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Monday, December 4, 2006 9:50:43 AM
Subject: Re: SQL Question


Thanks to all who responded.
The problem I am attempting to solve is as follows:
I have 3 files:
Order Header - 9M records
  Cust
  PO#
  Current Cancel Date YYMMDD

Order Detail - 2M records (Many to 1 for Order Header)
  Cust#
  PO#
  Div
  Brand
  Sub-Brand

Order Corrections - 1.3M records (Many to 1 for Order Header)
  Cust#
  PO#
  Correction Date YYMMDD
  Correction Time HHMMSS
  Old/New Code (O = Original)
  Original Cancel Date YYMMDD

Given a Div, Sub-Brand, List of Customers & List of Brands and Cancel
Date Range, I need to select a list of data with Div,Cust,PO# that meet
the selection criteria that has the date range for Original Cancel Date.
I have no problem with the SQL for selecting everything except the date
range.
If there is no Order Correction record, I want to use the Current Cancel
Date for selection.
Performance is extremely important.

All suggestions are appreciated.

TIA,

Jeff Young 
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
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 


  






----- Original Message ----
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Friday, December 1, 2006 2:57:49 PM
Subject: RE: SQL Question


Is this supposed to be a single row only?  Or could you have several
rows for the earlies date?  For now, I'll assume the second.

With Hx (Div, Acct, PO#, HxBegin) as
(    Select Division, Account, PO#, 
         Min(ChgDtCen*1000000 + ChgDt6)
    From     MyFile
    Group By Division, Account, PO#
)

Select a.* 
from     MyFile a inner join
     Hx on(a.Division=Hx.Div and 
         a.Account=Hx.Acct and
         a.PO#=Hx.PO# and
         ChgDtCen*1000000+ChgDt6=Hx.HxBegin)

hth,
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeff Young
Sent: Friday, December 01, 2006 1:13 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL Question


I have a history file with the following fields :
Division
Account
PO#
Date Changed YYMMDD
Date Changed Century  1/0
Cancel Date 
Old / New Code  O/N
.
.
.
.

Is there a way using SQL to select the data from the record with the
oldest date for a given Division, Account & PO#.

TIA,

Jeff Young 
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
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



________________________________________________________________________
____________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com