OLAP not allowed in Subquery

Database
Enthusiast

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;

1 REPLY
Junior Contributor

Re: OLAP not allowed in Subquery

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

Dieter