antonio.estima 4150 (9/25/2012)
Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.
Your query is executing in a different pattern in the two engines. WHERE clause does not fire prior to joins or any other operation. The entire statement is taken as a whole once you're done and works from there.
You got lucky for a long time in 2k5 and this just never altered the order in the engine on you. You cannot guarantee the WHERE clause firing first without using a few tricks. For an example of the most frustrating version of this process, do a google search on WHERE ISNUMERIC() error. It's quite common in EAV builds too.
Short version, you have two ways out of this. You have to procedurally control the query. The first is dumping only restricted data into a temp table and then using the temp table from there on out (my preferred method). The second is OPTION ( FORCE ORDER), in which you use a subquery for your WHERE clause'd table, then link the subquery as a table to the rest, like so:
select
a.*,
b.*
FROM
(SELECT * from tblA WHERE SomeCol <> 'PA') AS a
JOIN
tblB
ON a.SomeCol = b.SomeCol
OPTION ( FORCE ORDER)
You've overloaded a column, and your best option here is to clean up the source data and remove these workarounds eventually. There is no 'good' method to fix this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA