Home » SQL & PL/SQL » SQL & PL/SQL » Find Nth row inside an UPDATE (Oracle 10g and 11g)
| Find Nth row inside an UPDATE [message #427254] |
Wed, 21 October 2009 11:50  |
rolifantje
Messages: 18
Registered: September 2009
|
Junior Member |
|
|
Hello ....
I have two tables. In table A, I have some historical performance data for a horse. In table B, I have a field where I want to store the 5th most recent performance from A.
I have a query that works for 10g, but not for 11g.
UPDATE A_PERFORMANCE_INFO pi
SET FIFTHRECENT_DATE =
(
SELECT race_date
FROM
(
SELECT p.horse_code, p.race_date, DENSE_RANK() OVER (PARTITION BY p.horse_code ORDER BY p.race_date DESC) racedate_rank
FROM A_PERFORMANCE p
WHERE p.horse_code = pi.horse_code
AND p.race_date < pi.info_date
) B
WHERE b.racedate_rank = 5
)
WHERE pi.info_date = '21 oct 2009'
I get INVALID IDENTIFIER. I guess the most inner query cannot get a reference to the outer table.
Any feedback greatly appreciated.
To recreate the problem, run this SQL first.
DROP TABLE A_PERFORMANCE ;
CREATE TABLE A_PERFORMANCE ( RACE_DATE DATE, HORSE_CODE NUMBER(1) ) ;
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('10/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('09/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('08/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('07/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('06/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('05/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('04/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
DROP TABLE A_PERFORMANCE_INFO ;
CREATE TABLE A_PERFORMANCE_INFO ( INFO_DATE DATE, HORSE_CODE NUMBER(2), FIFTHRECENT_DATE DATE );
Insert into A_PERFORMANCE_INFO (INFO_DATE, HORSE_CODE, FIFTHRECENT_DATE) Values (TO_DATE('10/21/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6, NULL);
COMMIT;
|
|
|
|
| Re: Find Nth row inside an UPDATE [message #427263 is a reply to message #427254] |
Wed, 21 October 2009 12:16  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Thanks for the test case, but next time can you please use code tags - see the orafaq forum guide if you're not sure how.
That update shouldn't work in any version. It doesn't work on my 10g:
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
SQL> UPDATE A_PERFORMANCE_INFO pi
2 SET FIFTHRECENT_DATE =
3 (
4 SELECT race_date
5 FROM
6 (
7 SELECT p.horse_code, p.race_date,
DENSE_RANK() OVER (PARTITION BY p.horse_code ORDER BY p.race_date DESC) racedate_rank
8 FROM A_PERFORMANCE p
9 WHERE p.horse_code = pi.horse_code
10 AND p.race_date < pi.info_date
11 ) B
12 WHERE b.racedate_rank = 5
13 )
14 WHERE pi.info_date = '21 oct 2009'
15 /
AND p.race_date < pi.info_date
*
ERROR at line 10:
ORA-00904: "PI"."INFO_DATE": invalid identifier
I suspect your 10g db has a bug.
Sub-queries can only ever reference only queries that are 1 level removed.
To fix your problem you'll need to add A_PERFORMANCE_INFO to the from clause of your sub-sub-query. You'll need to copy the list line of your where clause into that queries where as well.
Also this:
WHERE pi.info_date = '21 oct 2009'
Is not a good idea - always to date dates.
It might work for you but a simple change of nls_date_format and it'll error.
[Updated on: Wed, 21 October 2009 12:35] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Find Nth row inside an UPDATE [message #427345 is a reply to message #427254] |
Thu, 22 October 2009 02:54 |
rolifantje
Messages: 18
Registered: September 2009
|
Junior Member |
|
|
Is there any reason why you can only refer to one level above?
I don't really understand understand how adding the outer WHERE clause to the sub-sub query will help. That would work for the race_date, but not for horse_code.
This query should update the FIFTHRECENT_DATE field for every horse_code for a given info_date.
Agreed, there is only one horse_code in the example that I posted, but in my real db there are many more of course. I don't think it would work.
|
|
|
|
|
|
| Re: Find Nth row inside an UPDATE [message #427359 is a reply to message #427345] |
Thu, 22 October 2009 03:58 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
rolifantje wrote on Thu, 22 October 2009 08:54I don't really understand understand how adding the outer WHERE clause to the sub-sub query will help. That would work for the race_date, but not for horse_code.
This query should update the FIFTHRECENT_DATE field for every horse_code for a given info_date.
Agreed, there is only one horse_code in the example that I posted, but in my real db there are many more of course. I don't think it would work.
If you give incomplete problem descriptions you'll get incomplete solutions I'm afraid.
|
|
|
|
| Re: Find Nth row inside an UPDATE [message #427373 is a reply to message #427359] |
Thu, 22 October 2009 05:01 |
rolifantje
Messages: 18
Registered: September 2009
|
Junior Member |
|
|
Hi Cookiemonster,
thx for your reply.
I don't think I misstated the problem, the horse_code was always there in the inner query.
Meanwhile I still don't know what to do. This is just one example, but I've been working with 10g for 3 years now, I've written a lot of stored procedures that depend on this bug/feature.
|
|
|
|
| Re: Find Nth row inside an UPDATE [message #427377 is a reply to message #427373] |
Thu, 22 October 2009 05:14 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
rolifantje wrote on Thu, 22 October 2009 11:01
Meanwhile I still don't know what to do. This is just one example, but I've been working with 10g for 3 years now, I've written a lot of stored procedures that depend on this bug/feature.
I think you've got a big problem then. You've basically made you DB unsupportable. Because if you encounter any other oracle bugs and need a patch-set to fix them you can't risk applying it as it might fix this bug and break all your code.
To be honest I'm amazed you get the correct results with this.
And you can forget about upgrading to 11g.
As for your original problem - why don't you re-post your test case - using code tags - and include a few different horse_codes in your sample data. Then hopefully one of the others who are better at analytics than me will take a look and sort you out.
|
|
|
|
|
|
| Re: Find Nth row inside an UPDATE [message #427395 is a reply to message #427391] |
Thu, 22 October 2009 06:48 |
rolifantje
Messages: 18
Registered: September 2009
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 22 October 2009 06:21Quote:but I've been working with 10g for 3 years now, I've written a lot of stored procedures that depend on this bug/feature.
Too bad you rely on a bug for your code.
Regards
Michel
Too bad I assumed Oracle would not introduce such a major bug.
This did not work in 9, I thought it was a new feature.
|
|
|
|
| Re: Find Nth row inside an UPDATE [message #427397 is a reply to message #427377] |
Thu, 22 October 2009 06:50 |
rolifantje
Messages: 18
Registered: September 2009
|
Junior Member |
|
|
cookiemonster wrote on Thu, 22 October 2009 05:14rolifantje wrote on Thu, 22 October 2009 11:01
Meanwhile I still don't know what to do. This is just one example, but I've been working with 10g for 3 years now, I've written a lot of stored procedures that depend on this bug/feature.
I think you've got a big problem then. You've basically made you DB unsupportable. Because if you encounter any other oracle bugs and need a patch-set to fix them you can't risk applying it as it might fix this bug and break all your code.
To be honest I'm amazed you get the correct results with this.
And you can forget about upgrading to 11g.
As for your original problem - why don't you re-post your test case - using code tags - and include a few different horse_codes in your sample data. Then hopefully one of the others who are better at analytics than me will take a look and sort you out.
This is the setup + query.
DROP TABLE A_PERFORMANCE ;
CREATE TABLE A_PERFORMANCE ( RACE_DATE DATE, HORSE_CODE NUMBER(1) ) ;
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('10/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('09/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('08/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('07/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('06/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('05/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('04/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('10/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('09/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('08/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('07/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('06/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('05/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('04/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
DROP TABLE A_PERFORMANCE_INFO ;
CREATE TABLE A_PERFORMANCE_INFO ( INFO_DATE DATE, HORSE_CODE NUMBER(2), FIFTHRECENT_DATE DATE );
Insert into A_PERFORMANCE_INFO (INFO_DATE, HORSE_CODE, FIFTHRECENT_DATE) Values (TO_DATE('10/21/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6, NULL);
Insert into A_PERFORMANCE_INFO (INFO_DATE, HORSE_CODE, FIFTHRECENT_DATE) Values (TO_DATE('10/21/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7, NULL);
COMMIT;
UPDATE A_PERFORMANCE_INFO pi
SET FIFTHRECENT_DATE =
(
SELECT race_date
FROM
(
SELECT p.horse_code, p.race_date, DENSE_RANK() OVER (PARTITION BY p.horse_code ORDER BY p.race_date DESC) racedate_rank
FROM A_PERFORMANCE p
WHERE p.horse_code = pi.horse_code
AND p.race_date < pi.info_date
) B
WHERE b.racedate_rank = 5
and b.horse_code = i.horse_code
and b.race_date = i.race_date
)
WHERE pi.info_date = '21 oct 2009' |
|
|
|
| Re: Find Nth row inside an UPDATE [message #427409 is a reply to message #427395] |
Thu, 22 October 2009 08:23 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
rolifantje wrote on Thu, 22 October 2009 13:48Michel Cadot wrote on Thu, 22 October 2009 06:21Quote:but I've been working with 10g for 3 years now, I've written a lot of stored procedures that depend on this bug/feature.
Too bad you rely on a bug for your code.
Regards
Michel
Too bad I assumed Oracle would not introduce such a major bug.
This did not work in 9, I thought it was a new feature.
I thought it too when 10g was released (and write a couple of things about it in AskTom and usenet) but this was fixed in the next patchset of your version.
Regards
Michel
[Updated on: Thu, 22 October 2009 08:24] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Dec 11 21:58:52 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
