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.
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
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.
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
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
,COALESCE(HIST.ROW_UPDATE_ID, SELL.ROW_INSERT_ID) AS A
FROM SELLTAB SELL
LEFT OUTER JOIN
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
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 :
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)