I'm trying to do a query that joins two tables. The trick is that I
only want it to return rows based on a certain criteria.

Table 1: Inventory
Fields: Inventory #, Description

Table 2: Prices
Fields: Inventory #, Price, Effective Year, Effective Month, Effective
Day

I want to return a row that gives me the Inventory #, Description, and
most current Price. I don't care what the effective date for the price
is, but I need it to be the most current one.

I was thinking along the lines of this:

SELECT Inventory.InvNo, Inventory.Description, Prices.Price
FROM Inventory, Price
WHERE EffYear = (SELECT MAX(EffYear) FROM Prices WHERE Prices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = Prices.InvNo

But the complication is that there may be 2 different prices from the
same year. If they hadn't set up the date to be in 3 different fields
it would be so much simpler. Any ideas are more than welcome!

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405111221.2fcc094@posting.google.co m...[color=blue]
> I'm trying to do a query that joins two tables. The trick is that I
> only want it to return rows based on a certain criteria.
>
> Table 1: Inventory
> Fields: Inventory #, Description
>
> Table 2: Prices
> Fields: Inventory #, Price, Effective Year, Effective Month, Effective
> Day
>
> I want to return a row that gives me the Inventory #, Description, and
> most current Price. I don't care what the effective date for the price
> is, but I need it to be the most current one.
>
> I was thinking along the lines of this:
>
> SELECT Inventory.InvNo, Inventory.Description, Prices.Price
> FROM Inventory, Price
> WHERE EffYear = (SELECT MAX(EffYear) FROM Prices WHERE Prices.InvNo =
> Inventory.InvNo)
> AND Inventory.InvNo = Prices.InvNo
>
> But the complication is that there may be 2 different prices from the
> same year. If they hadn't set up the date to be in 3 different fields
> it would be so much simpler. Any ideas are more than welcome![/color]

You could do something like this:

WITH NewPrices AS ( SELECT InvNo, Price, DATE(RTRIM(CHAR(EffYear)) || '-' ||
RTRIM(CHAR(EffMonth)) || '-' || RTRIM(CHAR(EffDay))) as EffDate FROM Prices)
SELECT Inventory.InvNo, Inventory.Description, NewPrices.Price
FROM Inventory, NewPrices
WHERE EffDate = (SELECT MAX(EffDate) FROM NewPrices WHERE NewPrices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = NewPrices.InvNo;

Essentially, the WITH clauses builds a temporary table containing all the
columns of the Price table, except that the EffYear/EffMonth/EffDay fields
are converted into a single DATE value which will make the comparison logic
in your query work the way you would like it to.

Note that this query is not neccessarily going to fast, especially if your
Prices table has a lot of rows in it.

If you're going to be doing queries like this on a regular basis, you might
want to look at rebuiding the Prices table with a better format for dates
(ie, using a single DATE column), or create a MQT (Materialized Query Table)
over the Prices table that does the date transformation.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405111221.2fcc094@posting.google.co m...[color=blue]
> I'm trying to do a query that joins two tables. The trick is that I
> only want it to return rows based on a certain criteria.
>
> Table 1: Inventory
> Fields: Inventory #, Description
>
> Table 2: Prices
> Fields: Inventory #, Price, Effective Year, Effective Month, Effective
> Day
>
> I want to return a row that gives me the Inventory #, Description, and
> most current Price. I don't care what the effective date for the price
> is, but I need it to be the most current one.
>
> I was thinking along the lines of this:
>
> SELECT Inventory.InvNo, Inventory.Description, Prices.Price
> FROM Inventory, Price
> WHERE EffYear = (SELECT MAX(EffYear) FROM Prices WHERE Prices.InvNo =
> Inventory.InvNo)
> AND Inventory.InvNo = Prices.InvNo
>
> But the complication is that there may be 2 different prices from the
> same year. If they hadn't set up the date to be in 3 different fields
> it would be so much simpler. Any ideas are more than welcome![/color]

You could do something like this:

WITH NewPrices AS ( SELECT InvNo, Price, DATE(RTRIM(CHAR(EffYear)) || '-' ||
RTRIM(CHAR(EffMonth)) || '-' || RTRIM(CHAR(EffDay))) as EffDate FROM Prices)
SELECT Inventory.InvNo, Inventory.Description, NewPrices.Price
FROM Inventory, NewPrices
WHERE EffDate = (SELECT MAX(EffDate) FROM NewPrices WHERE NewPrices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = NewPrices.InvNo;

Essentially, the WITH clauses builds a temporary table containing all the
columns of the Price table, except that the EffYear/EffMonth/EffDay fields
are converted into a single DATE value which will make the comparison logic
in your query work the way you would like it to.

Note that this query is not neccessarily going to fast, especially if your
Prices table has a lot of rows in it.

If you're going to be doing queries like this on a regular basis, you might
want to look at rebuiding the Prices table with a better format for dates
(ie, using a single DATE column), or create a MQT (Materialized Query Table)
over the Prices table that does the date transformation.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405111221.2fcc094@posting.google.co m...[color=blue]
> I'm trying to do a query that joins two tables. The trick is that I
> only want it to return rows based on a certain criteria.
>
> Table 1: Inventory
> Fields: Inventory #, Description
>
> Table 2: Prices
> Fields: Inventory #, Price, Effective Year, Effective Month, Effective
> Day
>
> I want to return a row that gives me the Inventory #, Description, and
> most current Price. I don't care what the effective date for the price
> is, but I need it to be the most current one.
>[/color]

Try

SELECT I.InvNo, I.Description, P.Price
FROM
)
SELECT I.*
, ROW_NUMBER() OVER(PARTITION BY InvNo ORDER BY EffYear Desc, EffMonth Desc,
EffDay Desc) as rn
FROM Inventory I
) as I
, Price P
WHERE I.rn = 1
AND I.InvNo = P.InvNo

BTW If {Inventory #, , Effective Year, Effective Month, Effective Day} is
not a key, then you might like to add Price into the ORDER BY to make the
query deterministic.


Regards
Paul Vernon
Business Intelligence, IBM Global Services

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405111221.2fcc094@posting.google.co m...[color=blue]
> I'm trying to do a query that joins two tables. The trick is that I
> only want it to return rows based on a certain criteria.
>
> Table 1: Inventory
> Fields: Inventory #, Description
>
> Table 2: Prices
> Fields: Inventory #, Price, Effective Year, Effective Month, Effective
> Day
>
> I want to return a row that gives me the Inventory #, Description, and
> most current Price. I don't care what the effective date for the price
> is, but I need it to be the most current one.
>[/color]

Try

SELECT I.InvNo, I.Description, P.Price
FROM
)
SELECT I.*
, ROW_NUMBER() OVER(PARTITION BY InvNo ORDER BY EffYear Desc, EffMonth Desc,
EffDay Desc) as rn
FROM Inventory I
) as I
, Price P
WHERE I.rn = 1
AND I.InvNo = P.InvNo

BTW If {Inventory #, , Effective Year, Effective Month, Effective Day} is
not a key, then you might like to add Price into the ORDER BY to make the
query deterministic.


Regards
Paul Vernon
Business Intelligence, IBM Global Services

I tried both Paul's and Matt's solutions, and both gave me compilation
errors. I neglected to mention that this query is being used to define
a cursor in a COBOL program. I don't know if that's why the errors are
occurring or not.

Paul's error was:
ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO
(their quotation from the code is way too long to cut/paste here, but
it's choking on the opening bracket that follows the "FROM", I think.

Matt's error was:
ILLEGAL SYMBOL "NEWTABLE"
DECLARE IPRD-CSR CURSOR FOR WITH NEWTABLE

I want to thank you guys for all your help, though. You've given me
some ideas - I'm seeing if I can modify what you gave me in a way that
will still make my COBOL DB2 code happy. If you have any more ideas
please let me know. :)


"Paul Vernon" <paul.vernon@ukk.ibmm.comm> wrote in message news:<c7t319$271s$1@gazette.almaden.ibm.com>...[color=blue]
> "Susan M." <suemolen@hotmail.com> wrote in message
> news:c58f20a7.0405111221.2fcc094@posting.google.co m...[color=green]
> > I'm trying to do a query that joins two tables. The trick is that I
> > only want it to return rows based on a certain criteria.
> >
> > Table 1: Inventory
> > Fields: Inventory #, Description
> >
> > Table 2: Prices
> > Fields: Inventory #, Price, Effective Year, Effective Month, Effective
> > Day
> >
> > I want to return a row that gives me the Inventory #, Description, and
> > most current Price. I don't care what the effective date for the price
> > is, but I need it to be the most current one.
> >[/color]
>
> Try
>
> SELECT I.InvNo, I.Description, P.Price
> FROM
> )
> SELECT I.*
> , ROW_NUMBER() OVER(PARTITION BY InvNo ORDER BY EffYear Desc, EffMonth Desc,
> EffDay Desc) as rn
> FROM Inventory I
> ) as I
> , Price P
> WHERE I.rn = 1
> AND I.InvNo = P.InvNo
>
> BTW If {Inventory #, , Effective Year, Effective Month, Effective Day} is
> not a key, then you might like to add Price into the ORDER BY to make the
> query deterministic.
>
>
> Regards
> Paul Vernon
> Business Intelligence, IBM Global Services[/color]

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405131154.5d18f230@posting.google.c om...[color=blue]
> I tried both Paul's and Matt's solutions, and both gave me compilation
> errors. I neglected to mention that this query is being used to define
> a cursor in a COBOL program. I don't know if that's why the errors are
> occurring or not.
>
> Paul's error was:
> ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO
> (their quotation from the code is way too long to cut/paste here, but
> it's choking on the opening bracket that follows the "FROM", I think.
>
> Matt's error was:
> ILLEGAL SYMBOL "NEWTABLE"
> DECLARE IPRD-CSR CURSOR FOR WITH NEWTABLE
>
> I want to thank you guys for all your help, though. You've given me
> some ideas - I'm seeing if I can modify what you gave me in a way that
> will still make my COBOL DB2 code happy. If you have any more ideas
> please let me know. :)
>
>[/color]
If you are on DB2 for z/OS or S/390 you should have stated so, and stated
your DB2 release number.


ROW_NUMBER() OVER is not available in version 7 or below on z/OS or 390, but
not sure about version 8.

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405131154.5d18f230@posting.google.c om...[color=blue]
> I tried both Paul's and Matt's solutions, and both gave me compilation
> errors. I neglected to mention that this query is being used to define
> a cursor in a COBOL program. I don't know if that's why the errors are
> occurring or not.
>
> Paul's error was:
> ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO
> (their quotation from the code is way too long to cut/paste here, but
> it's choking on the opening bracket that follows the "FROM", I think.
>
> Matt's error was:
> ILLEGAL SYMBOL "NEWTABLE"
> DECLARE IPRD-CSR CURSOR FOR WITH NEWTABLE
>
> I want to thank you guys for all your help, though. You've given me
> some ideas - I'm seeing if I can modify what you gave me in a way that
> will still make my COBOL DB2 code happy. If you have any more ideas
> please let me know. :)
>[/color]
select a.Inventory#, a.Description, b.Price
from table1 a, table2 b
where a.Inventory# = b.Inventory# and
b.effective_date = (select max(c.effective date) from table2 c
where b.Inventory# = c.Inventory#)

Note that I have taken the liberty to combine the dates into one column,
which is how they should be defined. If you insist on 3 separate columns
(ridiculous) the use the concat function ("||")to build a single value on
both sides of the "=" sign in the subselect.

> >[color=blue]
> select a.Inventory#, a.Description, b.Price
> from table1 a, table2 b
> where a.Inventory# = b.Inventory# and
> b.effective_date = (select max(c.effective date) from table2 c
> where b.Inventory# = c.Inventory#)
>
> Note that I have taken the liberty to combine the dates into one column,
> which is how they should be defined. If you insist on 3 separate columns
> (ridiculous) the use the concat function ("||")to build a single value on
> both sides of the "=" sign in the subselect.[/color]

Thanks Mark!

I didn't realize that SQL syntax varied that significantly between
OS's. I definitely should have mentioned at the beginning that I was
using OS/390.

This did the trick. I agree, it is ridiculous to split the date into 3
fields. Unfortunately when you work for a huge multinational, with
tons of programs supported by other people that read from the same
tables, you can't just redefine the table setup. So thanks for giving
me the workaround. It was a huge help.

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405141115.fda7bd6@posting.google.co m...[color=blue][color=green][color=darkred]
> > >[/color]
> > select a.Inventory#, a.Description, b.Price
> > from table1 a, table2 b
> > where a.Inventory# = b.Inventory# and
> > b.effective_date = (select max(c.effective date) from table2 c
> > where b.Inventory# = c.Inventory#)
> >
> > Note that I have taken the liberty to combine the dates into one column,
> > which is how they should be defined. If you insist on 3 separate columns
> > (ridiculous) the use the concat function ("||")to build a single value[/color][/color]
on[color=blue][color=green]
> > both sides of the "=" sign in the subselect.[/color]
>
> Thanks Mark!
>
> I didn't realize that SQL syntax varied that significantly between
> OS's. I definitely should have mentioned at the beginning that I was
> using OS/390.
>
> This did the trick. I agree, it is ridiculous to split the date into 3
> fields. Unfortunately when you work for a huge multinational, with
> tons of programs supported by other people that read from the same
> tables, you can't just redefine the table setup. So thanks for giving
> me the workaround. It was a huge help.[/color]

DB2 for z/OS will eventually have rownumber function (maybe in version 8
just released, you can check the manuals on the website to be sure). Note
that DB2 version 8 requires z/OS and does not run on OS/390.

"Susan M." <suemolen@hotmail.com> wrote in message
news:c58f20a7.0405141115.fda7bd6@posting.google.co m...[color=blue][color=green][color=darkred]
> > >[/color]
> > select a.Inventory#, a.Description, b.Price
> > from table1 a, table2 b
> > where a.Inventory# = b.Inventory# and
> > b.effective_date = (select max(c.effective date) from table2 c
> > where b.Inventory# = c.Inventory#)
> >
> > Note that I have taken the liberty to combine the dates into one column,
> > which is how they should be defined. If you insist on 3 separate columns
> > (ridiculous) the use the concat function ("||")to build a single value[/color][/color]
on[color=blue][color=green]
> > both sides of the "=" sign in the subselect.[/color]
>
> Thanks Mark!
>
> I didn't realize that SQL syntax varied that significantly between
> OS's. I definitely should have mentioned at the beginning that I was
> using OS/390.
>
> This did the trick. I agree, it is ridiculous to split the date into 3
> fields. Unfortunately when you work for a huge multinational, with
> tons of programs supported by other people that read from the same
> tables, you can't just redefine the table setup. So thanks for giving
> me the workaround. It was a huge help.[/color]

BTW, I would not consider the solution I gave as a "workaround," but is
probably the most straight forward solution. But others may have different
opinions.