Home » SQL & PL/SQL » SQL & PL/SQL » How to use window functions in where clause without inline view
How to use window functions in where clause without inline view [message #584677] Thu, 16 May 2013 13:14 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below is the query where I need to filter the data using window function.

I don't want to use inline view for mt query because i need to use the same query in OWB which it wont support.


Select a.Physical_Id,
       a.Booking_Begin_Date,
       a.Booking_End_Date,
       a.Country_Cd,
       Row_Number() Over(Partition By a.Physical_Id, a.Country_Cd, a.Booking_Begin_Date Order By a.Booking_Begin_Date) Rnk                           
                             From booking_mstr a
                            Where Row_Number() Over(Partition By a.Physical_Id, a.Country_Cd, a.Booking_Begin_Date Order By a.Booking_Begin_Date) =1;

I Cannot use below query in OWB Which is Inline View
----------------------------------------------------------------------------

Select b.* From 
(Select a.Physical_Id,
       a.Booking_Begin_Date,
       a.Booking_End_Date,
       a.Country_Cd,
       Row_Number() Over(Partition By a.Physical_Id, a.Country_Cd, a.Booking_Begin_Date Order By a.Booking_Begin_Date) Rnk                           
                             From booking_mstr a) Where b.rnk=1;





Any help really appreciated.

Thanks in advance
Re: How to use window functions in where clause without inline view [message #584679 is a reply to message #584677] Thu, 16 May 2013 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot.

Regards
Michel
Re: How to use window functions in where clause without inline view [message #585344 is a reply to message #584679] Thu, 23 May 2013 16:48 Go to previous message
Bill B
Messages: 1109
Registered: December 2004
Senior Member
But the query in a view and then call the view
Previous Topic: Procedure which converts salary
Next Topic: Replacing multiple characters
Goto Forum:
  


Current Time: Wed Oct 01 20:08:01 CDT 2014

Total time taken to generate the page: 0.12259 seconds