View previous topic :: View next topic
|
Author |
Message |
sasanka Warnings : 1 New User
Joined: 18 Jan 2008 Posts: 34 Location: India
|
|
|
|
Hi,
I have two tables SELLTAB and HISTSELL. HISTSELL table is the history table for SELLTAB ie when we we update any row of SELLTAB, before updating that row, we insert that row into HISTSELL.
The columns of table SELLTAB are
SELL_NO, SELL_TYPE, SELL_PRICE, LAST_ROW_INSERT_DATE, ROW_INSERT_ID.
And columns of table HISTSELL are
SELL_NO, SELL_TYPE, SELL_PRICE, LAST_ROW_UPDATE_DATE, ROW_UPDATE_ID.
Now I need a query to extract all rows with SELL_TYPE = M from table SELLTAB and table HISTSELL. Criteria for selecting rows from table HISTSELL is; if SELL_NO is available in both the tables then for that SELL_NO (with SELL_TYPE = M),we extract that row from table HISTSELL with maximum LAST_ROW_UPDATE_DATE.
We want result of the above query based on GROUP BY SELL_NO.
Some sample data of both the tables are as below
(1) Rows in SELLTAB table,
SELL_NO SELL_TYPE SELL_PRICE LAST_ROW_INSERT_DATE ROW_INSERT_ID
123 M 170.50 25-04-2011 A333
133 P 50.50 30-04-2011 S111
134 M 200.50 01-05-2011 P222
137 M 300.75 05-05-2011 W333
140 S 111.60 10-05-2011 R555
145 M 345.50 15-05-2011 A333
150 M 110.50 25-05-2011 X678
(2) Rows in HISTSELL table,
SELL_NO SELL_TYPE SELL_PRICE LAST_ROW_UPDATE_DATE ROW_UPDATE_ID
123 M 100.50 26-04-2011 Y333
123 M 120.50 28-04-2011 A333
123 M 150.50 30-04-2011 X333
133 P 40.50 01-05-2011 T111
137 M 250.75 15-05-2011 P333
137 M 270.75 20-05-2011 R333
So, query result should be as below
123 25-04-2011 A333
123 30-04-2011 X333
134 01-05-2011 P222
137 05-05-2011 W333
137 20-05-2011 R333
145 15-05-2011 A333
150 25-05-2011 X678
Row 123 30-04-2011 X333 is selected from HISTSELL table because highest LAST_ROW_UPDATE_DATE is 30-04-2011 among all the three rows with SELL_NO = 123 and SELL_TYPE = M.
Simillarly row 137 20-05-2011 R333 is selected from HISTSELL table among the two rows with SELL_NO = 137 and SELL_TYPE = M.
So, from HISTSELL table, we are selecting only that row with maximum
LAST_ROW_UPDATE_DATE and whose SELL_NO is present in table SELLTAB and with SELL_TYPE = M.
Can anybody please provide me such a query? |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
What have you done on your own? We may provide help but we won't do it for you! |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
This doesn't max sense :
* for each sell_no there is only 1 row in SELLTAB
* the requirements states : only the MAX(LAST_ROW_UPDATE_DATE) out of SELLHIST => 1 row
then why would you need to GROUP BY SELL_NO ?
===============
1) Are you able to write a query to get the row with MAX(LAST_ROW_UPDATE_DATE) from SELLHIST for 1 SELLNO ? (forget about SELLTAB for this exercise)
Many examples are found in this or other forums/fora.
==========
2) use the same technique but add joining to SELLTAB |
|
Back to top |
|
|
sasanka Warnings : 1 New User
Joined: 18 Jan 2008 Posts: 34 Location: India
|
|
|
|
Hi,
I am using the following query which gives the correct result
SELECT SELL.SELL_NO
,MAX(COALESCE(HIST.LAST_ROW_UPDATE_DATE, SELL.LAST_ROW_INSERT_DATE)
,COALESCE(HIST.ROW_UPDATE_ID, SELL.ROW_INSERT_ID)
FROM SELLTAB SELL
LEFT OUTER JOIN
HISTSELL HIST
ON SELL.SELL_NO = HIST.SELL_NO
WHERE SELL.SELL_TYPE = 'M';
But, I need the result of above query Group By SELL_NO. So in the above query, if I use GROUP BY SELL.SELL_NO, it gives error.
So can any one please provide a query which gives rows similar to the above query but results should be grouped by SELL_NO. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
So in the above query, if I use GROUP BY SELL.SELL_NO, it gives error. |
You have been part of the forum for more than 3 years - and apparently have not paid the slightest bit of attention. . .
Do Not Post "It didn't work"
Why have you not posted the error you received? |
|
Back to top |
|
|
sasanka Warnings : 1 New User
Joined: 18 Jan 2008 Posts: 34 Location: India
|
|
|
|
Hi,
I am really sorry as I didnot not explain about the error I got.
When I am using the query in the following way by using GROUP BY, getting error as below
SELECT SELL.SELL_NO
,MAX(COALESCE(HIST.LAST_ROW_UPDATE_DATE, SELL.LAST_ROW_INSERT_DATE)
,COALESCE(HIST.ROW_UPDATE_ID, SELL.ROW_INSERT_ID) AS A
FROM SELLTAB SELL
LEFT OUTER JOIN
HISTSELL HIST
ON SELL.SELL_NO = HIST.SELL_NO
WHERE SELL.SELL_TYPE = 'M'
GROUP BY SELL.SELL_NO, A;
The error I am getting is,
SQLCODE = -206, ERROR: A IS NOT VALID IN THE CONTEXT WHERE IT IS USED
SQLSTATE = 42703 SQLSTATE RETURN CODE |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Concerning the error :
It says : 'A IS NOT VALID IN THE CONTEXT WHERE IT IS USED '
you use A on 2 places :
- "as A"
- in "group by"
one of those two isn't allowed: guess !
I'm just gonna give a solution because this thread is going nowhere :
Code: |
SELECT SELL.SELL_NO
,COALESCE(HIST.LAST_ROW_UPDATE_DATE, SELL.LAST_ROW_INSERT_DATE)
,COALESCE(HIST.ROW_UPDATE_ID, SELL.ROW_INSERT_ID)
from selltab sell
left join (select * from histsell hist1
where hist1.LAST_ROW_UPDATE_DATE =
(select max(hist2.LAST_ROW_UPDATE_DATE) from histsell hist2
where hist1.sellno = hist2.sellno)
) hist on sell.sellno = hist.sellno
|
(there are other solution depending on performance) |
|
Back to top |
|
|
|