× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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.