By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,369 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

Plea for help creating SQL query

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a

"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...
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!


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
Nov 12 '05 #2

P: n/a

"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...
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!


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
Nov 12 '05 #3

P: n/a
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...
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.


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
Nov 12 '05 #4

P: n/a
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...
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.


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
Nov 12 '05 #5

P: n/a
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" <pa*********@ukk.ibmm.comm> wrote in message news:<c7***********@gazette.almaden.ibm.com>...
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...
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.


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

Nov 12 '05 #6

P: n/a
"Susan M." <su******@hotmail.com> wrote in message
news:c5**************************@posting.google.c om...
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. :)

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.
Nov 12 '05 #7

P: n/a
"Susan M." <su******@hotmail.com> wrote in message
news:c5**************************@posting.google.c om...
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. :)

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.
Nov 12 '05 #8

P: n/a
> >
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.


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.
Nov 12 '05 #9

P: n/a
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...

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.


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.


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.
Nov 12 '05 #10

P: n/a
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...

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.


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.


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.
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.