Home » SQL & PL/SQL » SQL & PL/SQL » 2 inner queries in select statement??
2 inner queries in select statement?? [message #209171] Wed, 13 December 2006 10:30 Go to next message
rpiller
Messages: 2
Registered: December 2006
Junior Member
I have 2 inner queries in my select statement. The most inner query I need to use the most outer query info in the where clause. I then want to order by a certain column. Then the query above that takes only the first rownum, so it only returns 1 value. The problem is when I do this it doesn't reconginze the outer table alias. With only 1 nested query I can reference the outer table alias, but with 2 nested queries it doesn't let me it seems. Any workarounds for this?

This works, but I can't do order by in the inner query and as is it doesn't return the right value.
select fore.station, fore.temp_date, fore.temp_hour,
            (select act.temp_date
            from ldfcst_weather_actual act
            where act.temp_date between to_date(fore.temp_date - 365)-30 and to_date(fore.temp_date - 365)+30
            and act.temp_hour = 0
            and act.temp between fore.temp-3 and fore.temp+3
            and rownum = 1
            ) found_date
from ldfcst_weather_forecast fore


This doesn't work because it doesn't know what fore is in the most inne query.
select fore.station, fore.temp_date, fore.temp_hour,
    (select temp_date
       from
            (select act.temp_date, abs(act.temp_date - fore.temp_date - 365) temp_date_tol, abs(act.temp - fore.temp) temp_tol 
            from ldfcst_weather_actual act
            where act.temp_date between to_date(fore.temp_date - 365)-30 and to_date(fore.temp_date - 365)+30
            and act.temp_hour = 0
            and act.temp between fore.temp-3 and fore.temp+3
            and rownum = 1
            order by temp_date --temp_tol, temp_date_tol
            )
        where rownum = 1
     ) find_date
from ldfcst_weather_forecast fore

[Updated on: Wed, 13 December 2006 10:30]

Report message to a moderator

Re: 2 inner queries in select statement?? [message #209186 is a reply to message #209171] Wed, 13 December 2006 11:53 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Try it like this.

select fore.station, fore.temp_date, fore.temp_hour, found_date.temp_date
from ldfcst_weather_forecast fore,
            (select act.temp_date
            from ldfcst_weather_actual act
            and act.temp_hour = 0
            ) found_date
where fore.{join column}=found_date.{join column}
and act.temp_date between to_date(fore.temp_date - 365)-30 and to_date(fore.temp_date - 365)+30
and act.temp between fore.temp-3 and fore.temp+3
/


Although, your code is sloppy as you are using a TO_DATE on either a DATE column (not necessary) or a VARCHAR2 column which is asking for trouble with implicit conversion.

[Updated on: Wed, 13 December 2006 11:56]

Report message to a moderator

Re: 2 inner queries in select statement?? [message #209194 is a reply to message #209171] Wed, 13 December 2006 13:22 Go to previous message
rpiller
Messages: 2
Registered: December 2006
Junior Member
I don't see how that would work. Basically for every row in the outer table (forecast) I need to do a "search" against the the actual table and find the closest date within a specific tolerance for date and temp, and only return the 1 closest. This looks like it will return multiple records from the actual table.
Previous Topic: ORA-02019 while accesing DB B from DB A through a dblink.
Next Topic: as of Timestamp and Flashback Setting
Goto Forum:
  


Current Time: Sat Dec 10 12:47:35 CST 2016

Total time taken to generate the page: 0.07436 seconds