Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SQL92

5 posts in General Discussion Last posting was on 2005-10-07 01:29:19.0Z
Chris Ceniza Posted on 2005-10-04 06:19:36.0Z
Sender: 703a.43421dd9.1804289383@sybase.com
From: Chris Ceniza
Newsgroups: ianywhere.public.general
Subject: SQL92
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <43421e6b.703e.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 3 Oct 2005 23:17:15 -0700, 10.22.241.42
Lines: 8
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 Oct 2005 23:17:16 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 Oct 2005 23:19:36 -0700
X-Trace: forums-1-dub 1128406776 10.22.108.75 (3 Oct 2005 23:19:36 -0700)
X-Original-Trace: 3 Oct 2005 23:19:36 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-2-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4828
Article PK: 17433

Which is more SQL92 compliant ASE or ASA?

Is "WITH" common table expression SQL92 compliant?


Thanks,

Chris


Glenn Paulley Posted on 2005-10-04 14:01:17.0Z
Newsgroups: ianywhere.public.general
Subject: Re: SQL92
From: Glenn Paulley <paulley@ianywhere.com>
References: <43421e6b.703e.1681692777@sybase.com>
Organization: iAnywhere Solutions
Message-ID: <Xns96E5658895AF0paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-Trace: 4 Oct 2005 06:58:52 -0700, paulley-t41.sybase.com
Lines: 54
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Oct 2005 06:58:55 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Oct 2005 07:01:17 -0700
X-Trace: forums-1-dub 1128434477 10.22.108.75 (4 Oct 2005 07:01:17 -0700)
X-Original-Trace: 4 Oct 2005 07:01:17 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-2-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4830
Article PK: 8567

All of the major vendors advertise compliance with the core ANSI
standard, though as usual the devil is in the details. In terms of query
expressions, ASA has greater support for recent additions to the ANSI
standard including

- "WITH" named query expressions (which are in SQL 99, not in SQL 92)
- LATERAL derived tables
- recursive UNION (for bill-of-materials queries)
- FULL OUTER JOIN
- OLAP functionality: extensions to the GROUP BY clause, window and
ranking functions, statistical functions, etc.

Also, ASA supports PSM stored procedures "the Watcom dialect". ASE's
Transact-SQL dialect for stored procedures does not match the ANSI
standard as closely as ASA's dialect does, though there are (sometimes
subtle) differences between both dialects and the ANSI standard. Also,
ASA supports PSM user-defined functions, while in ASE user-defined
functions must be written in Java. It is helpful to remember that Sybase
offered one of the earliest commercial implementations of stored
procedures long before ANSI entrenched the PSM dialect as a standard
(and, unlike the ANSI committee, we do have to concern ourselves with
supporting legacy applications).

Finally, SQL 92 is obsolete (it is 13 years old, after all). The current
SQL standard is SQL 2003.

Glenn

Chris Ceniza wrote in news:43421e6b.703e.1681692777@sybase.com:

> Which is more SQL92 compliant ASE or ASA?
>
> Is "WITH" common table expression SQL92 compliant?
>
>
> Thanks,
>
> Chris
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


Chris Ceniza Posted on 2005-10-06 06:08:32.0Z
Sender: 11cb.4344be34.1804289383@sybase.com
From: Chris Ceniza
Newsgroups: ianywhere.public.general
Subject: Re: SQL92
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4344bec6.11d2.1681692777@sybase.com>
References: <Xns96E5658895AF0paulleyianywherecom@10.22.241.106>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 5 Oct 2005 23:05:58 -0700, 10.22.241.42
Lines: 62
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 5 Oct 2005 23:06:00 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 5 Oct 2005 23:08:32 -0700
X-Trace: forums-1-dub 1128578912 10.22.108.75 (5 Oct 2005 23:08:32 -0700)
X-Original-Trace: 5 Oct 2005 23:08:32 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-2-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4834
Article PK: 8570

In terms of compliancy, which is more ANSI SQL compliant
ORACLE or ASA?

> All of the major vendors advertise compliance with the
> core ANSI standard, though as usual the devil is in the
> details. In terms of query expressions, ASA has greater
> support for recent additions to the ANSI standard
> including
>
> - "WITH" named query expressions (which are in SQL 99, not
> in SQL 92) - LATERAL derived tables
> - recursive UNION (for bill-of-materials queries)
> - FULL OUTER JOIN
> - OLAP functionality: extensions to the GROUP BY clause,
> window and ranking functions, statistical functions, etc.
>
> Also, ASA supports PSM stored procedures "the Watcom
> dialect". ASE's Transact-SQL dialect for stored
> procedures does not match the ANSI standard as closely as
> ASA's dialect does, though there are (sometimes subtle)
> differences between both dialects and the ANSI standard.
> Also, ASA supports PSM user-defined functions, while in
> ASE user-defined functions must be written in Java. It is
> helpful to remember that Sybase offered one of the
> earliest commercial implementations of stored procedures
> long before ANSI entrenched the PSM dialect as a standard
> (and, unlike the ANSI committee, we do have to concern
> ourselves with supporting legacy applications).
>
> Finally, SQL 92 is obsolete (it is 13 years old, after
> all). The current SQL standard is SQL 2003.
>
> Glenn
>
> Chris Ceniza wrote in
> news:43421e6b.703e.1681692777@sybase.com:
>
> > Which is more SQL92 compliant ASE or ASA?
> >
> > Is "WITH" common table expression SQL92 compliant?
> >
> >
> > Thanks,
> >
> > Chris
> >
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288


Glenn Paulley Posted on 2005-10-06 11:25:59.0Z
Newsgroups: ianywhere.public.general
Subject: Re: SQL92
From: Glenn Paulley <paulley@ianywhere.com>
References: <Xns96E5658895AF0paulleyianywherecom@10.22.241.106> <4344bec6.11d2.1681692777@sybase.com>
Organization: iAnywhere Solutions
Message-ID: <Xns96E74B2B58691paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: vpn-dub-052.sybase.com
X-Original-Trace: 6 Oct 2005 04:23:24 -0700, vpn-dub-052.sybase.com
Lines: 117
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 6 Oct 2005 04:23:26 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 6 Oct 2005 04:25:59 -0700
X-Trace: forums-1-dub 1128597959 10.22.108.75 (6 Oct 2005 04:25:59 -0700)
X-Original-Trace: 6 Oct 2005 04:25:59 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-2-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4835
Article PK: 8569

Your question is too broad to answer (especially in a newsgroup post). My
first counter-question would be "with respect to what?". Keep in mind
that the SQL 2003 standard consists of 9 volumes totalling thousands of
pages: Volume 2, "Foundation" covers DML and DDL statements, schema
definition, transaction semantics, referential integrity support, and
lots of other things. The list is finite, but still very long.

However, let me make a few points:

- Oracle does not support ANSI isolation levels of concurrency -
transactions only get "snapshot isolation" semantics where a transaction
"sees" a consistent state of the database as of transaction start, and is
isolated from the effects of other concurrent transactions.

Aside: SQL Anywhere 9.x does not support snapshot isolation, but we plan
to in the Jasper release (as an option: the other existing isolation
levels will continue to be supported).

- Oracle's PL/SQL dialect is considerably different from ANSI PSM, though
as I said earlier all of the SQL dialects have minor (or major)
differences from PSM.

- Each system supports a great deal of what is defined in the
"Foundation" of the 2003 standard, again with various minor differences.
For example, Oracle does not support recursive queries as per the
standard, but uses a different construction altogether. SQL Anywhere,
along with IBM's DB2, does support recursive UNION as per SQL 2003. As
another example, Oracle's syntax for "upsert" (insert or update) was
adopted in its entirety by the ANSI committee, so Oracle is compliant
with that statement's definition. In contrast, SQL Anywhere supports
"INSERT ... ON EXISTING" which predates the publication date of ANSI SQL
2003.

Is there a specific item that you have concerns over?

Glenn

Chris Ceniza wrote in news:4344bec6.11d2.1681692777@sybase.com:

> In terms of compliancy, which is more ANSI SQL compliant
> ORACLE or ASA?
>
>> All of the major vendors advertise compliance with the
>> core ANSI standard, though as usual the devil is in the
>> details. In terms of query expressions, ASA has greater
>> support for recent additions to the ANSI standard
>> including
>>
>> - "WITH" named query expressions (which are in SQL 99, not
>> in SQL 92) - LATERAL derived tables
>> - recursive UNION (for bill-of-materials queries)
>> - FULL OUTER JOIN
>> - OLAP functionality: extensions to the GROUP BY clause,
>> window and ranking functions, statistical functions, etc.
>>
>> Also, ASA supports PSM stored procedures "the Watcom
>> dialect". ASE's Transact-SQL dialect for stored
>> procedures does not match the ANSI standard as closely as
>> ASA's dialect does, though there are (sometimes subtle)
>> differences between both dialects and the ANSI standard.
>> Also, ASA supports PSM user-defined functions, while in
>> ASE user-defined functions must be written in Java. It is
>> helpful to remember that Sybase offered one of the
>> earliest commercial implementations of stored procedures
>> long before ANSI entrenched the PSM dialect as a standard
>> (and, unlike the ANSI committee, we do have to concern
>> ourselves with supporting legacy applications).
>>
>> Finally, SQL 92 is obsolete (it is 13 years old, after
>> all). The current SQL standard is SQL 2003.
>>
>> Glenn
>>
>> Chris Ceniza wrote in
>> news:43421e6b.703e.1681692777@sybase.com:
>>
>> > Which is more SQL92 compliant ASE or ASA?
>> >
>> > Is "WITH" common table expression SQL92 compliant?
>> >
>> >
>> > Thanks,
>> >
>> > Chris
>> >
>>
>>
>>
>> --
>> Glenn Paulley
>> Research and Development Manager, Query Processing
>> iAnywhere Solutions Engineering
>>
>> EBF's and Patches: http://downloads.sybase.com
>> choose SQL Anywhere Studio >> change 'time frame' to all
>>
>> To Submit Bug Reports:
>> http://casexpress.sybase.com/cx/cx.stm
>>
>> SQL Anywhere Studio Supported Platforms and Support Status
>> http://my.sybase.com/detail?id=1002288
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


Chris Ceniza Posted on 2005-10-07 01:29:19.0Z
Sender: 6a35.4345cd6c.1804289383@sybase.com
From: Chris Ceniza
Newsgroups: ianywhere.public.general
Subject: Re: SQL92
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4345cf6f.6a45.1681692777@sybase.com>
References: <Xns96E74B2B58691paulleyianywherecom@10.22.241.106>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Oct 2005 18:29:19 -0700
X-Trace: forums-1-dub 1128648559 10.22.241.41 (6 Oct 2005 18:29:19 -0700)
X-Original-Trace: 6 Oct 2005 18:29:19 -0700, 10.22.241.41
Lines: 132
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4843
Article PK: 8574

Thanks.

I'll keep in touch if there are still things that I would
like to know about SQL standards.

Chris

> Your question is too broad to answer (especially in a
> newsgroup post). My first counter-question would be "with
> respect to what?". Keep in mind that the SQL 2003
> standard consists of 9 volumes totalling thousands of
> pages: Volume 2, "Foundation" covers DML and DDL
> statements, schema definition, transaction semantics,
> referential integrity support, and lots of other things.
> The list is finite, but still very long.
>
> However, let me make a few points:
>
> - Oracle does not support ANSI isolation levels of
> concurrency - transactions only get "snapshot isolation"
> semantics where a transaction "sees" a consistent state
> of the database as of transaction start, and is isolated
> from the effects of other concurrent transactions.
>
> Aside: SQL Anywhere 9.x does not support snapshot
> isolation, but we plan to in the Jasper release (as an
> option: the other existing isolation levels will continue
> to be supported).
>
> - Oracle's PL/SQL dialect is considerably different from
> ANSI PSM, though as I said earlier all of the SQL
> dialects have minor (or major) differences from PSM.
>
> - Each system supports a great deal of what is defined in
> the "Foundation" of the 2003 standard, again with various
> minor differences. For example, Oracle does not support
> recursive queries as per the standard, but uses a
> different construction altogether. SQL Anywhere, along
> with IBM's DB2, does support recursive UNION as per SQL
> 2003. As another example, Oracle's syntax for "upsert"
> (insert or update) was adopted in its entirety by the
> ANSI committee, so Oracle is compliant with that
> statement's definition. In contrast, SQL Anywhere supports
> "INSERT ... ON EXISTING" which predates the publication
> date of ANSI SQL 2003.
>
> Is there a specific item that you have concerns over?
>
> Glenn
>
> Chris Ceniza wrote in
> news:4344bec6.11d2.1681692777@sybase.com:
>
> > In terms of compliancy, which is more ANSI SQL compliant
> > ORACLE or ASA?
> >
> >> All of the major vendors advertise compliance with the
> >> core ANSI standard, though as usual the devil is in
> the >> details. In terms of query expressions, ASA has
> greater >> support for recent additions to the ANSI
> standard >> including
> >>
> >> - "WITH" named query expressions (which are in SQL 99,
> not >> in SQL 92) - LATERAL derived tables
> >> - recursive UNION (for bill-of-materials queries)
> >> - FULL OUTER JOIN
> >> - OLAP functionality: extensions to the GROUP BY clause
> , >> window and ranking functions, statistical functions,
> etc. >>
> >> Also, ASA supports PSM stored procedures "the Watcom
> >> dialect". ASE's Transact-SQL dialect for stored
> >> procedures does not match the ANSI standard as closely
> as >> ASA's dialect does, though there are (sometimes
> subtle) >> differences between both dialects and the ANSI
> standard. >> Also, ASA supports PSM user-defined
> functions, while in >> ASE user-defined functions must be
> written in Java. It is >> helpful to remember that Sybase
> offered one of the >> earliest commercial implementations
> of stored procedures >> long before ANSI entrenched the
> PSM dialect as a standard >> (and, unlike the ANSI
> committee, we do have to concern >> ourselves with
> supporting legacy applications). >>
> >> Finally, SQL 92 is obsolete (it is 13 years old, after
> >> all). The current SQL standard is SQL 2003.
> >>
> >> Glenn
> >>
> >> Chris Ceniza wrote in
> >> news:43421e6b.703e.1681692777@sybase.com:
> >>
> >> > Which is more SQL92 compliant ASE or ASA?
> >> >
> >> > Is "WITH" common table expression SQL92 compliant?
> >> >
> >> >
> >> > Thanks,
> >> >
> >> > Chris
> >> >
> >>
> >>
> >>
> >> --
> >> Glenn Paulley
> >> Research and Development Manager, Query Processing
> >> iAnywhere Solutions Engineering
> >>
> >> EBF's and Patches: http://downloads.sybase.com
> >> choose SQL Anywhere Studio >> change 'time frame' to
> all >>
> >> To Submit Bug Reports:
> >> http://casexpress.sybase.com/cx/cx.stm
> >>
> >> SQL Anywhere Studio Supported Platforms and Support
> Status >> http://my.sybase.com/detail?id=1002288
> >
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288