RE: SQL Question

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

 Order_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 bit of SQL assumes that every order header has at least one order
detail record, if that is not the case then you will have to do:

 

Select

 a.cust,

 a.PO,

 coalesce(c.correction_date, a.current_cancel_date),

 b.div,

 b.brand,

 b.subbrand

From

 Order_Header a left outer join

 Order_detail b on

  a.cust = b.cust and

  b.div = 'SOME DIv' and

  b.sub-brand = 'Stuff'

  a.PO = b.PO left outer join

 Order_Correction c on

  a.cust = c.cust and

  a.po = c.po

Where

 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

 

From your description it sounds like all you need is the coalesce

function, which returns the first of its arguments that is not NULL,
handy for outer joins where a field you need could easily come back NULL
if the record is not matched.

 

Chris

 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Monday, December 04, 2006 9:51 AM
To: Midrange Systems Technical Discussion
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