Re: SQL Question

[Jeff Young <cooljeff913@xxxxxxxxx>]

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