× 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.


  1.  Home
  2. MIDRANGE-L
  3. December 2006

Re: SQL Question

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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.