Hi,

This runs on 9.02 - but not on V11 (because I read deprecation in V10)

Object Error: [Sybase][ODBC Driver][SQL Anywhere]Transact-SQL outer joins
are currently disabled

Driver String: Select INNHEAD.company, custnumber, INNHEAD.city,
INNHEAD.state, INNHEAD.phone, recommended, passcode2,deleted,INNVAR.COMPANY
from DBA.INNHEAD,DBA.INNVAR where (DBA.INNVAR.VARID =*
DBA.INNHEAD.VARCONNECT AND DBA.INNVAR.VARID <> 0) AND INNHEAD.Company like
'%test%' order by INNHEAD.COMPANY,INNHEAD.CITY


a) Should I just reenable it this

-or

b) Rewrite this ... and if so, what is the proper rewrite for it

And why is this deprecated? Just curious so I understand.

Thank you.

Transact-SQL outer joins were offered as a compatibility feature for SQL
Anywhere with Sybase ASE. However, ASE has supported ANSI outer join
syntax from Version 12 onwards, so the compatibility issue has lessened
considerably.

Since there are various semantic issues with TSQL outer joins that still
exist, we took the decision to deprecate them and have them disabled by
default. A white paper that describes the semantic issues with TSQL
outer joins in SQL Anywhere can be found at

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

Glenn


Robert Paresi wrote:
--
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/datab...chnicalsupport

You could just set it back on for now ... but given this
state, then next time you upgrade you will most likely
need to visit this again and there the redress may only
be to address the syntax.

T/SQL outer joins are historical, encumbered with
old behaviour and are non-standard in behaviour.

Move the join to the From clause as in this rewrite

Select INNHEAD.company, . . .
From DBA.INNHEAD inh left outer joint DBA.INNVAR inv
on inv.VARID = inh.VARCONNECT
where inv.VARID <> 0
AND inh.Company like '%test%'
order by inh.COMPANY,inh.CITY;
-- note my use of table aliases is only a convenience for me and not
required.


Select INNHEAD.company, custnumber, INNHEAD.city,
INNHEAD.state, INNHEAD.phone, recommended, passcode2,deleted,INNVAR.COMPANY
from DBA.INNHEAD,DBA.INNVAR

where (DBA.INNVAR.VARID =*
DBA.INNHEAD.VARCONNECT AND DBA.INNVAR.VARID <> 0) AND INNHEAD.Company like
'%test%' order by INNHEAD.COMPANY,INNHEAD.CITY


"Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote

Thank you.

I rewrote the query as you suggested.

-Robert

"Nick Elson [Sybase iAnywhere]" <@nick@dot@elson@at@sybase@dot@com@> wrote
in message news:4adf26ff$1 (AT) forums-1-dub (DOT) ..

Nick has unfortunately demonstrated precisely why we have deprecated
TSQL outer joins in the SQL Anywhere; the rewritten query below is not
semantically equivalent to your original TSQL one. The additional
predicate in the WHERE clause,

because it is NULL-intolerant, effectively turns the query into an inner
join because that predicate will eliminate null-supplied rows (from the
inv (or INNVAR) table) where inv.VARID would be NULL.

A correct rewriting would be

Select INNHEAD.company, . . .
From DBA.INNHEAD inh left outer join DBA.INNVAR inv
on ( inv.VARID = inh.VARCONNECT and inv.VARID <> 0)
Where inh.Company like '%test%'
order by inh.COMPANY, inh.CITY;

Examples of this begin on page 7 of the whitepaper.

Glenn

Robert Paresi wrote:
--
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/datab...chnicalsupport

Hi Robert,

Also, if you were to code against Microsoft SQL Server, the old style outer
join syntax is depracated in SQL Server 2005 with message 4147.

Cheers,

Shao

"Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote

Same think in SQL Server 2008...

1> SELECT *
2> FROM t1, t2
3> WHERE t1.c *= t2.c
4> GO
Msg 4147, Level 15, State 1, Server BRECK-PC\TSUNAMI, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run
this query w
ithout modification, please set the compatibility level for current
database to
80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is
strongly r
ecommended to rewrite the query using ANSI outer join operators (LEFT
OUTER JOIN
, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI
join operato
rs will not be supported even in backward-compatibility modes.

Breck

On 21 Oct 2009 09:34:30 -0700, "Robert Paresi"
<FirstInitialLastName (AT) innquest (DOT) com> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

Glenn is absolutely correct on that.

[in my defense my first draft was the same as
Glenn's but I thought that might confuse and
would be simpler had I changed it the way
I did ... alas ... best intentions do not always
yield the best results ;-)]

Let us know if we have confused you in any
way here.


"Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote