All Forums Database
Malvi Jaggi 4 posts Joined 09/11
18 Sep 2013
OLAP not allowed in Subquery

I am coding a SQL ( and I know a solution which is working). However I want to fix an alternative code. The code intents to find the first monday after the last friday of the last month( Here I have taken  sample date of last day of 2013, otherwise it is a generic query and you can replace the '2013-12-31' by date)

select
  calendar_date
from
  sys_calendar.calendar
where
  day_of_calendar =
  (
    select
      day_of_calendar + 10
    from
      sys_calendar.calendar
    where
      month_of_calendar =
      (
        select
          month_of_calendar - 1
        from
          sys_calendar.calendar
        where
          calendar_date = '2013-12-31'
      )
      and day_of_week = 6
    qualify
      row_number() over(
      order by
        calendar_date desc
      ) = 1
  )

 

The problem that I' m facing is an error : 3706:  Syntax error: Ordered Analytical Functions are not allowed in subqueries. 

Output directed to Answerset window.

 

However when I running a part of the above SQL, its working even though it is also using an OLAP in a Subquery. Can anyone point me out the reason for the error?

 

This query is running fine :

select
  day_of_calendar + 10
from
  sys_calendar.calendar
where
  month_of_calendar =
  (
    select
      month_of_calendar - 1
    from
      sys_calendar.calendar
    where
      calendar_date = '2013-12-31'
  )
  and day_of_week = 6
qualify
  ROW_NUMBER() OVER(
  ORDER BY
    calendar_date DESC
  ) = 1;

 

 

dnoeth 4628 posts Joined 11/04
18 Sep 2013

Well, the 2nd QUALIFY is definitely not part of the subquery :-)
 
Dieter

Dieter

You must sign in to leave a comment.