SQL Anywhere 11 - Transact-SQL outer joins currently disabled?

[Vlad]

Hi,

SQL Anywhere 11.0.0.59

We're just investigating migrating our SQL 9.0.2 database to 11, we did
unload/reload which seemed to go fine, all views got translated
successfully, but when we ran it through our application, on several queries
we got the error message

Database error code = -1099
SQLSTATE = S1000
Transact-SQL outer joins are currently disabled

We looked in sysoptions table for tsql_outer_joins parameter (was set to
Off), but had no access rights to change parameter from OFF to ON, so we
went through Sybase Central - DB options list. We couldn't change the value,
so we removed the tsql_outer_joins parameter altogether, and re-created it
with "On" setting.

Is this OK? Why were outer joins disabled in the first place?

Thanks,
Vlad

[Glenn Paulley [Sybase iAnywhere]]

Transact-SQL outer joins are deprecated. The TSQL_OUTER_JOINS connection
option was added to version 10 with a default of OFF so that it had to
be explicitly enabled prior to the use or compilation of any TSQL outer
join query (including those in a stored procedure or a view).

One can change the value of the option for any connection by using the
SET OPTION statement.

Glenn

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

To Submit Bug Reports: http://case-express.sybase.com

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

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport

[Andrei Iliev]

Glenn, what was the reason to depricate OUTER JOIN? How to do same as
outer join whithout outer join?

Andrei Iliev

Glenn Paulley [Sybase iAnywhere] пишет:

[Glenn Paulley [Sybase iAnywhere]]

It's only Transact-SQL outer joins that are deprecated, not all outer
joins; I strongly recommend that you rewrite your queries to use ANSI
outer join syntax, ie

SELECT <select list>
FROM R LEFT OUTER JOIN S ON ( <condition> )

which has well-defined behaviour.

For a description of the issues surrounding Transact-SQL outer joins,
take a look at this whitepaper:

http://www.sybase.com/detail?id=1017447

Glenn

[Ivan T. Bowman]

You may also wish to consider the REWRITE( sql, 'ANSI' ) builtin function.
This function can show you the equivalent of a SQL statement with TSQL outer
joins replaced with the ANSI equivalent syntax.

--
Ivan T. Bowman
SQL Anywhere Research and Development

"Glenn Paulley [Sybase iAnywhere]" <pau...@ianywhere.com> wrote in message
news:490db128$1@forums-1-dub...

> It's only Transact-SQL outer joins that are deprecated, not all outer
> joins; I strongly recommend that you rewrite your queries to use ANSI
> outer join syntax, ie
>
> SELECT <select list>
> FROM R LEFT OUTER JOIN S ON ( <condition> )
>
> which has well-defined behaviour.
>
> For a description of the issues surrounding Transact-SQL outer joins, take
> a look at this whitepaper:
>
> http://www.sybase.com/detail?id=1017447
>
> Glenn
>
> Andrei Iliev wrote:
>> Glenn, what was the reason to depricate OUTER JOIN? How to do same as
>> outer join whithout outer join?
>>
>> Andrei Iliev
>>

>> Glenn Paulley [Sybase iAnywhere] ?????:

[Passby]

I am using SQL Anywhere 10 (port from 9) with the same
problem
I've found solution.
In Sybase Central, connect to the reloaded database.
Then select File->Options, click option 'tsql_outer_joins'
and type 'On' in the 'Value:' field. Finally press 'Set
Permanent Now'.
Done.

[Stephen Rice [Sybase]]

Yes, That is what Glenn said in his original post.

His recommendation still stands. Since we have deprecated TSQL Outer Joins
we could remove that functionality from a future version of SQL Anywhere.
You should start converting your SQL now to avoid a potential panic in the
future.

--
/steve
Stephen Rice, Sybase iAnywhere
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Docs : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<Passby> wrote in message news:495b1c18.453...@sybase.com...

[Mark Culp]

And to add to Steve's comment, please take the time to read
Glenn's excellent white paper on why you don't want to use
TSQL outer joins:

http://www.sybase.com/detail?id=1017447

- Mark