Thread: sql2008 diff sql2003

Hello

I found one usefull article
http://iablog.sybase.com/paulley/2008/07/sql2008-now-an-approved-iso-international-standard/

Regards
Pavel Stehule

Pavel Stehule escribió:
> Hello
> 
> I found one usefull article
> http://iablog.sybase.com/paulley/2008/07/sql2008-now-an-approved-iso-international-standard/

Wow, this is really horrid:
#  F856 through F859: FETCH FIRST clause in subqueries, views,and query expressions. The SQL:2008 syntax for
restrictingtherows of a result set is FETCH FIRST, rather than Microsoft SQLServer’s SELECT TOP N equivalent which SQL
Anywheresupportspresently. 
 

This means we have to support stuff like

declare foo cursor for select * from lists;
select * from (fetch first from foo) as bar;

(I wonder why didn't they use FETCH NEXT instead.  As is, it seems a bit
cumbersome to use.)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

>>>>> "Alvaro" == Alvaro Herrera <alvherre@commandprompt.com> writes:
Alvaro> Wow, this is really horrid:
Alvaro>  # F856 through F859: FETCH FIRST clause in subqueries,Alvaro>  views, and query expressions. The SQL:2008
syntaxforAlvaro>  restricting the rows of a result set is FETCH FIRST, ratherAlvaro>  than Microsoft SQL Server’s
SELECTTOP N equivalent whichAlvaro>  SQL Anywhere supports presently.
 
Alvaro> This means we have to support stuff like
Alvaro> declare foo cursor for select * from lists;Alvaro> select * from (fetch first from foo) as bar;

No, that's wrong.

The new syntax is:

<query expression> ::= [ <with clause> ] <query expression body>     [ <order by clause> ] [ <result offset clause> ] [
<fetchfirst clause> ]
 

<result offset clause> ::= OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::= FETCH { FIRST | NEXT } [ <fetch first row count> ] { ROW | ROWS } ONLY

so it's like this:

select * from foo order by bar offset 5 rows fetch first 10 rows only;

(nothing that I can see assigns any semantics to FIRST vs NEXT, they seem
to do the same thing)

-- 
Andrew (irc:RhodiumToad)

Andrew Gierth wrote:

>  Alvaro> This means we have to support stuff like
> 
>  Alvaro> declare foo cursor for select * from lists;
>  Alvaro> select * from (fetch first from foo) as bar;
> 
> No, that's wrong.

[...]

> so it's like this:
> 
> select * from foo order by bar offset 5 rows fetch first 10 rows only;

Oh, I see -- it's just a cumbersome way to have our LIMIT clause.
What's the "ONLY" for?


> (nothing that I can see assigns any semantics to FIRST vs NEXT, they seem
> to do the same thing)

I was actually thinking that you'd be able to open a cursor and then
invoke the query repeatedly.  With FETCH FIRST it wouldn't work, because
every repetition would get the same rows, but with FETCH NEXT it'd give
you a paginated query.

It seems a lot less useful this way.

-- 
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 48' 55.3", W 73º 15' 24.7"
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)

>>>>> "Alvaro" == Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> so it's like this:>> >> select * from foo order by bar offset 5 rows fetch first 10 rows only;
Alvaro> Oh, I see -- it's just a cumbersome way to have our LIMITAlvaro> clause.  What's the "ONLY" for?

It seems to be just a mandatory noise word.

-- 
Andrew.