Post reply

problem with old style joins *=

  • October 15, 2003 at 9:44 am

    #160174

    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

    #478334

    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

    #478341

    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

    #478400

    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

    #478474

    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

    #478487

    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.

    Randy
    Helpdesk: Perhaps Im not the only one that does not know what you are doing. šŸ˜‰

  • October 17, 2003 at 2:42 am

    #478598

    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

    #478599

    quote:

    Also be careful using -- to comment.

    any issues with that?

    Frank

    http://www.insidesql.de

    --
    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

    #478613

    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

    #478615

    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

    http://www.insidesql.de

    --
    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

    #478629

    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

    #478643

    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

    #478647

    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.

    Randy
    Helpdesk: Perhaps Im not the only one that does not know what you are doing. šŸ˜‰

  • October 19, 2003 at 3:07 pm

    #478726

    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