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 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #427273 is a reply to message #427254] Wed, 21 October 2009 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I guess the most inner query cannot get a reference to the outer table.

You can reference only fields that are ONE level above, no more.

Regards
Michel
Re: Find Nth row inside an UPDATE [message #427345 is a reply to message #427254] Thu, 22 October 2009 02:54 Go to previous messageGo to next message
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 #427355 is a reply to message #427345] Thu, 22 October 2009 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any reason why you can only refer to one level above?

It is the standard.

Regards
Michel
Re: Find Nth row inside an UPDATE [message #427359 is a reply to message #427345] Thu, 22 October 2009 03:58 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
rolifantje wrote on Thu, 22 October 2009 08:54
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.


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #427391 is a reply to message #427373] Thu, 22 October 2009 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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
Re: Find Nth row inside an UPDATE [message #427395 is a reply to message #427391] Thu, 22 October 2009 06:48 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
Michel Cadot wrote on Thu, 22 October 2009 06:21
Quote:
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 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
cookiemonster wrote on Thu, 22 October 2009 05:14
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.



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 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rolifantje wrote on Thu, 22 October 2009 13:48
Michel Cadot wrote on Thu, 22 October 2009 06:21
Quote:
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

Previous Topic: changing data with FOR UPDATE..WHERE CURRENT OF (merged 7)
Next Topic: Forcing SQL to show none existent date entries
Goto Forum:
  


Current Time: Fri Dec 09 08:11:27 CST 2016

Total time taken to generate the page: 0.11890 seconds