Re: SQL Question

["rick baird" <rick.baird@xxxxxxxxx>]

Jeff,   try this:    I didn't include the order header/detail in it,
until we get this part working - then we can add the join to the
header/detail

please reply whether it works or not.

select a.AdjAct,
        a.AdjPO#,
        a.AdjDat,
        a.AdjTim,
        min(a.AdjDat * 1000000 + a.AdjTim) as a.AdjDtm,
        a.AdjON,
        a.AdjCan,
        a.AdjCac
from  OrhAdjhp a
join (select b.AdjAct,
                 b.AdjPO#,
                 min(AdjDat * 1000000 + AdjTim) as b.AdjDtm,
                 b.AdjON,
                 b.AdjCan,
                 b.AdjCac
        from OrhAdjhp b
    group by b.AdjAct,
                 b.AdjPO#,
                 b.AdjON,
                 b.AdjDtm,
                 b.AdjCan,
                 b.AdjCac
    order by b.AdjAct,
                 b.AdjPO#,
                 b.AdjON,
                 b.AdjDtm,
                 b.AdjCan,
                 b.AdjCac    ) b
 on a.AdjAct = b.AdjAct
and a.AdjPO# = b.AdjPO#
and a.AdjON = b.AdjON
and a.AdjDtm = b.AdjDtm
and a.AdjCan = b.AdjCan
and a.AdjCac = b.AdjCac


because you're joining on a derived field, performance isn't going to
be great, but running it in debug mode might offer suggestions for
access paths to build.

Rick

On 12/4/06, Jeff Young <cooljeff913@xxxxxxxxx> wrote:
> 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
>
>
> * Denotes multiple
> 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: Chris Payne <CPayne@xxxxxxxxxxxxxxx>
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Sent: Monday, December 4, 2006 10:55:40 AM
> 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-----
> 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
> --
> 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.
>
>
>
> --
> 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.
>
>
>
> ________________________________________________________________________
> ____________
> Yahoo! Music Unlimited
> Access over 1 million songs.
> http://music.yahoo.com/unlimited
> --
> 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.
>
>
>
> ________________________________________________________________________
> ____________
> Cheap talk?
> Check out Yahoo! Messenger's low PC-to-Phone call rates.
> http://voice.yahoo.com
> --
> 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.
>
>
> --
> 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.
>
>
>
> ____________________________________________________________________________________
> Do you Yahoo!?
> Everyone is raving about the all-new Yahoo! Mail beta.
> http://new.mail.yahoo.com
> --
> 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.