Re: SQL Question

[Billy Rowe <billyrowe@xxxxxxx>]

 -- The SQL just needs a little tweaking, This "Common Table" expression will 
return the
 -- columns from the corrections table for earliest cust/po row that exists( if 
one does... )
 -- Then in the "Select" you are just joining to the "Common table" retrieving 
the corresponding
 -- cust/po row columns if one exists:
 With Earliest_Correction as (
  select oc.cust#, oc.po#, ( Correction_Date_YYMMDD * 1000000 + Correction_Time 
HHMMSS ) as first_oc,
         oc.on_code, oc.org_can_date
   from  order_correction oc,
         ( select Cust#, PO#, min( Correction_Date_YYMMDD * 1000000 + 
Correction_Time HHMMSS )
            from  order_correction a
           where exists ( select null from order_header where cust#  =  a.cust# 
and  po#  =  a.po# )
           group by Cust#, PO# ) moc
  where  oc.cust#                 =  moc.cust#
   and   oc.po#                   =  moc.po#
   and   oc.correction_timestamp  =  moc.correction_timestamp )
 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 ) between start_date 
and end_date

   hth

 Chris,
 I tried your solution, but retreived multiple records from the correction file 
when I had the same Acct & PO with different cancel dates.
 The SQL I used was :
 select AdjAct,AdjPO#,min(AdjDat * 1000000 + AdjTim) AdjDtm,    
 AdjON,AdjCan,AdjCac from OrhAdjhp where adjon = 'O' and adjdlt =
 ' ' group by AdjAct,AdjPO#,AdjOn,AdjCan,AdjCaC                 

 The following is a subset of my results:
  ACCT   CUSTOMER                          ADJDTM   OLD   CANCEL   CENTURY
  NO.    PO#                                        NEW   DATE            
 A013M  3095                                  60,706,162,422    O      60,705   
   1 
 A013M  3270                                  61,023,161,151    O      61,020   
   1  *
 A013M  3270                                  61,019,210,036    O      61,022   
   1  *
 A042M  ABS91505                         51,121,172,500    O      51,129      1 

 ----- Original Message ----
 Subject: 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-----
 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#.


 ----- Original Message ----
 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