JOIN vs =*

Question

JOIN vs =*

Alicia-391625

Mr or Mrs. 500

Points: 508
More actions
February 4, 2007 at 10:28 pm

#172018

Hi, I'm having problem to run the query with =*/*= in SQl2005 where this return me error message as below:
"The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended 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 operators will not be supported even in backward-compatibility modes."
Understand that SQL 2005 no longer support ambiguous operator ("*=" or "=*"). There are some query I need to replace it with JOIN statement from my previous SQL2000 server. But I'm facing some logic constraint with JOIN statement. Is there anyone can help me to look into this statement how to replace JOIN with (=*/*=). I'd struggling with this statement for whole noon... Appreciate if anyone from here can provide the help.
select *
from
pack_pin pppm,
market_prod mp
,
market_pack dsg
,
material sap
where
mp.ver_ind = 'R'
and
mp.ipkg_idn = dsg.ipkg_idn
and
mp.d_name *= rtrim(sap.d_name)
and
mp.qdf_cde *= isnull(rtrim(sap.spec_cde),'')
and
dsg.dsg_nme *= isnull(rtrim(sap.pkg_cde),'')
and
pppm.std_id = 'Y'
and
pppm.ver_ind = 'R'
and
pppm.pkg_cde = 'N/A'
and
pppm.chr_idn =* mp.chr_idn
and
pppm.pin_char_idn =* mp.pin_char_idn

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

SQL Server has long supported two forms of OUTER JOIN syntax, the ANSI syntax (using LEFT OUTER JOIN, RIGHT OUTER JOIN, etc.), and the simplified T-SQL syntax (using *= and =*). If you've always used ANSI syntax then you are safe, but if you have any existing code that uses the simplified T-SQL syntax, that code will not run on SQL Server 2005, and the following error message will be returned:

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended 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 operators will not be supported even in backward-compatibility modes.

Fortunately, that message also provides the solution. Using the sp_dbcmptlevel stored procedure you set the backwards compatibility level so that the old style outer joins work. Until you manually fix them all, that is.

MohammedU
Microsoft SQL Server MVP

Alicia-391625 Mr or Mrs. 500 Points: 508 More actions · Answer 2

Thanks MohammedU. Understand that this can get fixed will reversing the backward compatibility level. But since we are doing the migration, we decided not to reverse it to keep the our DB object always compact to the environment. So, what we are doing now is to manually fix those =*/*= statement. When come to this statement, I'm having logic constraint if using LEFT/RIGHT OUTER JOIN statement to get the same result. Is there any advise on how to arrange this statement's logic?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Seems this baby has it all - inner, left and right joins mixed together.

It's very hard to figure out how to rearrange it, without knowing more on how the tables are related, and the purpose of the query.

I'd suggest that you take a step back, analyze what the query is supposed to deliver, and rewrite it step by step. That's probably the easiest method.

/Kenneth

Ken McKelvey SSCoach Points: 18843 More actions · Answer 4

It has been some time since I have converted outer joins from old style to new style format, however there are generally two points to bear in mind:

1. With the old style the * is against the table where all the rows are kept. ie Assuming the columns are specified in the same order as the tables, *= is a LEFT JOIN and =* is a RIGHT JOIN. (You need to watch for when the columns are specified in a different order to the tables!)

2. With the new style syntax, filtering in the ON clause produces different results to filtering on the WHERE clause for OUTER joins.

eg With:

SELECT *

FROM A

LEFT JOIN B ON A.Aid = B.Bid AND B.SomeColumn = 'B'

the table B is filtered so that it only contains B.SomeColum = 'B' before the LEFT JOIN is done. (ie All rows in table A will be returned.)

This is normally what is required.

With:

SELECT *

FROM A

LEFT JOIN B ON A.Aid = B.Bid

WHERE B.SomeColumn = 'B'

the LEFT JOIN is done first and then rows where B.SomeColumn <> 'B' are removed. This potentially means that not all rows in table A are returned.

When converting from old style to new style outer joins, the second option normally produces the same results but the first option may be what is really required.

Given that you have not provided any sample data and results, it is very difficult to know how to convert your query. At a guess I would try either:

SELECT *

FROM market_prod mp

JOIN market_pack dsg

ON mp.ipkg_idn = dsg.ipkg_idn

AND mp.ver_ind = 'R'

LEFT JOIN material sap

ON mp.d_name = rtrim(sap.d_name)

AND mp.qdf_cde = isnull(rtrim(sap.spec_cde),'')

AND dsg.dsg_nme = isnull(rtrim(sap.pkg_cde),'')

LEFT JOIN pack_pin pppm

ON mp.chr_idn = pppm.chr_idn

AND mp.pin_char_idn = pppm.pin_char_idn

WHERE pppm.std_id = 'Y'

AND pppm.ver_ind = 'R'

AND pppm.pkg_cde = 'N/A'

or:

SELECT *

FROM market_prod mp

JOIN market_pack dsg

ON mp.ipkg_idn = dsg.ipkg_idn

AND mp.ver_ind = 'R'

LEFT JOIN material sap

ON mp.d_name = rtrim(sap.d_name)

AND mp.qdf_cde = isnull(rtrim(sap.spec_cde),'')

AND dsg.dsg_nme = isnull(rtrim(sap.pkg_cde),'')

LEFT JOIN pack_pin pppm

ON mp.chr_idn = pppm.chr_idn

AND mp.pin_char_idn = pppm.pin_char_idn

AND pppm.std_id = 'Y'

AND pppm.ver_ind = 'R'

AND pppm.pkg_cde = 'N/A'

Ken McKelvey SSCoach Points: 18843 More actions · Answer 5

P.S. I did my guess conversion using the WHERE clause on the following basis:

1. moving the INNER JOINs to the top. (The only INNER JOIN was there already.)

2. converting all =* to *=.

3. forming the new FROM clause by specifying the tables in the order they appear in the old WHERE clause.

This heuristic is a good place to start and generally works unless the query is particularly involved or the original coder was trying to be obscure.

Alicia-391625 Mr or Mrs. 500 Points: 508 More actions · Answer 6

Wow! Thanks alot Enthusiast!!

After I posted my problem yest I keep on this JOIN replacement. Ha!

Option 2 has generate the result I want. Thanks for the guideline in converting *=/=*. This is very useful for me to keep my work on the rest of the view conversion.