October 15, 2003 at 9:44 am
I have a select query that gives inconsistent result sets that is based on tables with static data. Output of either 7 rows or 83050 rows.
This query (included below) will return 2 different result sets that appears to happen randomly or maybe it depends on the lunar alignment and how many doughnuts I've eaten recently. Seriously, the only thing I could think of was to rewrite the query using the new style syntax of joins.
When I rewrote the query using the new join syntax (ansi standard left joins and inner joins), I get consistent results every time. This is unsettling because if old style queries are not returning the same results everytime then I have a lot of conversions to do in a hurry.
Has anyone experienced flakiness with SQL2000 SP3 and the old style joins?
SELECT
b.tot_deps_growth_rate_12
FROM book_values_dt b, book_values_dt b1, book_values_dt b2, client_info c
WHERE
b.client_nbr *= b1.client_nbr
AND b.client_nbr *= b2.client_nbr
AND b.svc *= b1.svc AND b.svc *= b2.svc
AND b.segment *= b1.segment AND b.segment *= b2.segment
AND b.period =CASE WHEN b.client_nbr IN (5988,23042,24438) THEN 200306 ELSE 200303 END
AND b1.period =CASE WHEN b1.client_nbr IN (5988,23042,24438) THEN 200212 ELSE 200209 END
AND b2.period =CASE WHEN b2.client_nbr IN (5988,23042,24438) THEN 200206 ELSE 200203 END
AND b.client_nbr = c.client_nbr --remove *
AND b.category =1
AND b.segment = 0 AND b.svc = 'BC'
AND c.client_nbr IN
(5988,
7462,
24382,
24438,
61435,
24317,
23042)
ORDER BY b.short_name
October 15, 2003 at 10:48 am
Check out the Books OnLine. Specifically, use the Index search tab and enter:
Joins-SQL Server. Then double click on the sub-topic Transact-SQL.
That explains that using =* or *= can cause ambiguity.
-SQLBill
October 15, 2003 at 11:20 am
I first noticed that happen in SQL 7. I have been using the SQL-92 syntax JOIN syntax ever since. Once you get used to writing your queries this way you will find them much easier to read and write.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 16, 2003 at 2:28 am
Agree.
Always use the SQL92 syntax. Outer joins weren't standardized before this and can return unusual results. -down to the fact that the WHERE clause is not associative. See BOL.
October 16, 2003 at 8:11 am
I ran into the problem when we first went to 2000. I found the sections in the BOL that basically tell you not to use them.
Basically, this is another great reason to have SP's instead of queries in the client apps (whether fat client or web). You only have one place to change the code. Of course, you may have a boat-load of SP's to change...
Joe Johnson
NETDIO,LLC.
Joe Johnson
NETDIO,LLC.
October 16, 2003 at 8:55 am
The conversion might be tricky. In the old style, the where clause is applied before the join while in the new style the where is applied after the join. This can make a big difference in outer joins. The On clause can include part of the where clause to reduce the records before the join; however, Iām often surprised with the results. And watch out for null values in the On clause. A join with a derived table with the appropriate where clause might be one alternative.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. š
October 17, 2003 at 2:42 am
I have just spend a month removing those from SP's. Other no-no's for 2000 are <> Null or = NULL. These should be replaced by IS NULL or IS NOT NULL. Locks should use "with" and index references should be like this - WITH (INDEX(indx_name)).
Also be careful using -- to comment.
October 17, 2003 at 2:51 am
quote:
Also be careful using -- to comment.
any issues with that?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 17, 2003 at 5:21 am
It was another DBA in our company that did an Audit for us on 6.5 to 2000 upgrade. This is what he reported----
In SQL 6.5, the upgrade changes the -- by removing it. This allows that command to execute in the stored procedure.
All comments should be changed to */ instead to avoid any problems in the future.
Replace
-- this is a comment
with
/* this is a comment */
October 17, 2003 at 5:26 am
quote:
In SQL 6.5, the upgrade changes the -- by removing it. This allows that command to execute in the stored procedure.All comments should be changed to */ instead to avoid any problems in the future.
how do you say?
Shot oneself in the foot.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 17, 2003 at 7:18 am
Thanks for everyone's input. By the way I have experienced problems using /* */ for commenting (in stored procedures) when there is an embedded GO in the comments. Changing each comment line to begin with double dash -- solved that problem.
quote:
It was another DBA in our company that did an Audit for us on 6.5 to 2000 upgrade. This is what he reported----In SQL 6.5, the upgrade changes the -- by removing it. This allows that command to execute in the stored procedure.
All comments should be changed to */ instead to avoid any problems in the future.
Replace
-- this is a comment
with
/* this is a comment */
October 17, 2003 at 8:25 am
Has anyone written any code, in any language, that would translate a select statement from the old style join syntax to ansi standard join syntax?
Our software works with multiple database engines, at least one of which does not support ansi standard join syntax. We already have a parsing algorithm that translates from one type of DB to another, but automatically translating from the old style syntax would seem to be significantly more difficult.
Thanks,
-bob
-bob
-bob
October 17, 2003 at 8:39 am
quote:
...the upgrade changes the -- by removing it....
This sounds like a serious bug to me. What's next, removing quote comment delimiters in VB upgrades.
I use the "--" syntax except when debugging. Doing so avoids nesting or batch problems with the the /* */ syntax.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. š
October 19, 2003 at 3:07 pm
Just confirming some of the comments made.
http://www.microsoft.com/sql/techinfo/tips/development/July23.asp
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply