Queries with OVER

Queries with OVER

  
Hello all

I've having, for some time now, a warning using SS 5.1 (but this comes back from 4.X if I remember correctly) that I cannot explain, and to be honest doesn't make any sense to me, so if anyone can show me the light, I'd be most appreciated :)

If you have an advanced query with something like:
SELECT *
FROM (
  SELECT 1949 + ROW_NUMBER() OVER (ORDER BY ID) AS Yr
  FROM {USER_MASTER}
  ) Years
WHERE Yr <= YEAR(GETDATE())

This would return a list of years from 1950 forward (using the USER_MASTER table but you could use any other table really, USER_MASTER you are sure that will always exists, at lest up to v5.1 ;) )

The warning I always get is: 
Unexpected SQL
Unexpected '(' in SQL statement of GetYears.

The '(' in the error message refers to the parentheses between OVER and ORDER. Well considering that this is the correct syntax for the OVER clause in SQL Server (see here) why the warning?

Best regards,
PC
Hi Pedro,

Unfortunatly the SQL parser we use on the validator does not cover all the possible sql syntax.

It will work fine, just give that warning. You can hide the warning by using Right Click -> Hide Message.

Regards
João Rosado
Hello Pedro,

   I create the list of years using this SQL code:



with yearlist as  
(  
    select YEAR(GetDate()) - @NumberOF_LastYears  as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= YEAR(GetDate())
)
select year from yearlist order by year desc


the parameter "@NumberOF_LastYears" is the number of years that I plan to have in the list.
For example: 10, the result would be:

2011
2010
2009
2008
2007
2006
2005
2004
2003
2002
2001



Best Regards
Bruno Rebelo

 
WOW ..i just looked he query itself  and saw what you were trying to do...comeon guys what about creating a little action with a foreach/if/assign/list append.
If it's on 5.1+ you can even use the "Cache" property in eSpace actions.

Putting the load on the database server / connection delays is usually not the best solution.

Regards,
João Rosado
@Bruno: really nice, never had seen that done but i's pretty slick, not sure I can use it in the my particular case though.

@João: an action is not an option when you have the need of doing it interactively nested in other queries...
hi Pedro,

you can try like this :

with yearlist as  
(  
    select 1950 as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= YEAR(GetDate())
)
select year from yearlist order by year desc




This are called Recursive Queries using Common Table Expressions in sql Server.

regards,
Bruno Rebelo
Hi Pedro,

What is exactly your case then? Can't think in many situations that you need to generate a list of years on the fly for a query that can't be replaced by a simpler and faster post-processing.

On the Bruno case is just a direct query to get it ...and there is no justification for it.

Regards,
João Rosado
@João:
This is definetly not a common situation. I cannot use a post-processing list, because, as I mentioned, I need to use the list inside the query. In this example you see a list of years, but with the appropriate changes, you can turn it in a different list, a timeline varying from seconds to years for instance
Hi Pedro, 

can you share the business requirment to see if that is the only solution to solve it?

Thanks
RNA