Professional Web Applications Themes

Wrong query result when using join - Microsoft SQL / MS SQL Server

Hi all, I run the query below on two tables having exactly the same structure. One being an older copy of the other. Purpose of the query is to show differences in one column called bookedreserve. select a.*, b.bookedreserve as oldbookedreserve from (select office,treaty,uwy,origccyid, bookingyear ,cyr,cym,bookedclaimno, sum(bookedreserve) as bookedreserve from baselmovements_recons_usd group by office,treaty,uwy,origccyid,bookingyear,cyr,cym,bo okedclaim no) a INNER JOIN (select office,treaty,uwy,origccyid,bookingyear, cyr,cym,bookedclaimno, sum(bookedreserve) as bookedreserve from baselmovements_recons_usd06 group by office,treaty,uwy,origccyid,bookingyear,cyr,cym,bo okedclaim no) b ON b.office = a.office and b.treaty = a.treaty and b.uwy = a.uwy and b.origccyid = a.origccyid and b.bookingyear = a.bookingyear and b.cyr = a.cyr and b.cym = ...

  1. #1

    Default Wrong query result when using join

    Hi all,

    I run the query below on two tables having exactly the
    same structure. One being an older copy of the other.
    Purpose of the query is to show differences in one column
    called bookedreserve.

    select a.*, b.bookedreserve as oldbookedreserve
    from
    (select office,treaty,uwy,origccyid,
    bookingyear ,cyr,cym,bookedclaimno, sum(bookedreserve) as
    bookedreserve
    from baselmovements_recons_usd
    group by
    office,treaty,uwy,origccyid,bookingyear,cyr,cym,bo okedclaim
    no) a
    INNER JOIN
    (select office,treaty,uwy,origccyid,bookingyear,
    cyr,cym,bookedclaimno, sum(bookedreserve) as bookedreserve
    from baselmovements_recons_usd06
    group by
    office,treaty,uwy,origccyid,bookingyear,cyr,cym,bo okedclaim
    no) b
    ON
    b.office = a.office and b.treaty = a.treaty and b.uwy =
    a.uwy and b.origccyid = a.origccyid and
    b.bookingyear = a.bookingyear and b.cyr = a.cyr and b.cym
    = a.cym and b.bookedclaimno = a.bookedclaimno
    where b.bookedreserve <> a.bookedreserve
    order by 1,2,3,4,6,7,5,8

    When I add "and treaty = 'TR 17872A'" before the order by
    statement I got a subset of the result where one record
    was not contained in the previous result !!!

    Does anyone have an idea ?

    Many thanks.
    Didier Guest

  2. #2

    Default RE: Wrong query result when using join

    Hello,

    Thank you for your posting. From your post I was not able to get a clear
    idea of the issue you encoutered. Will you provide us with the following
    information to clarify it?

    1. What is the exact record you inserted? Did you insert the record in
    table baselmovements_recons_usd? Please post detailed DDLs of the table and
    statement to insert record and we will try to repoudce this on our side.

    2. Which record you got from that was not in previous query? I am not very
    clear about what you mean by "When I add "and treaty = 'TR 17872A'" before
    the order by statement I got a subset of the result where one record was
    not contained in the previous result"

    If you have other related information, please also feel free to let me
    know.

    Thanks & Regards,

    Peter Yang
    MCSE2000, MCSA, MCDBA
    Microsoft Partner Online Support

    Get Secure! - www.microsoft.com/security

    ================================================== ===
    When responding to posts, please "Reply to Group" via
    your newsreader so that others may learn and benefit
    from your issue.
    ================================================== ===
    This posting is provided "AS IS" with no warranties, and confers no rights.


    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Didier" <com>
    | Sender: "Didier" <com>
    | Subject: Wrong query result when using join
    | Date: Tue, 15 Jul 2003 03:08:03 -0700
    | Lines: 38
    | Message-ID: <03b701c34ab8$fa97dc00$gbl>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | cht="iso-8859-1"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | Thread-Index: AcNKuPqXt8J0CodbTXek0SmelvRHgg==
    | Newsgroups: microsoft.public.sqlserver.programming
    | Path: cpmsftngxa06.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:373586
    | NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | Hi all,
    |
    | I run the query below on two tables having exactly the
    | same structure. One being an older copy of the other.
    | Purpose of the query is to show differences in one column
    | called bookedreserve.
    |
    | select a.*, b.bookedreserve as oldbookedreserve
    | from
    | (select office,treaty,uwy,origccyid,
    | bookingyear ,cyr,cym,bookedclaimno, sum(bookedreserve) as
    | bookedreserve
    | from baselmovements_recons_usd
    | group by
    | office,treaty,uwy,origccyid,bookingyear,cyr,cym,bo okedclaim
    | no) a
    | INNER JOIN
    | (select office,treaty,uwy,origccyid,bookingyear,
    | cyr,cym,bookedclaimno, sum(bookedreserve) as bookedreserve
    | from baselmovements_recons_usd06
    | group by
    | office,treaty,uwy,origccyid,bookingyear,cyr,cym,bo okedclaim
    | no) b
    | ON
    | b.office = a.office and b.treaty = a.treaty and b.uwy =
    | a.uwy and b.origccyid = a.origccyid and
    | b.bookingyear = a.bookingyear and b.cyr = a.cyr and b.cym
    | = a.cym and b.bookedclaimno = a.bookedclaimno
    | where b.bookedreserve <> a.bookedreserve
    | order by 1,2,3,4,6,7,5,8
    |
    | When I add "and treaty = 'TR 17872A'" before the order by
    | statement I got a subset of the result where one record
    | was not contained in the previous result !!!
    |
    | Does anyone have an idea ?
    |
    | Many thanks.
    |

    Peter Guest

  3. #3

    Default RE: Wrong query result when using join



    Hi Peter,

    thanks for your interest. Actually I could step further in my
    investigations.

    The tables I'm trying to compare with join have both the same size i.e
    each about 1'000'000 records !
    Therefore I wanted to extract some data out of it to give you some stuff
    to try out.
    When I tried to reproduce the problem with this portion It didn't happen
    again i.e the result contained all expected records.

    I ran it again for the whole data with SQL Profiler open and.. I had a
    "Sort Warning".
    When I ran the query again without "order by" I got no "Sort Warning"
    and the result is right.
    So the problem has to do with limitations of our SQL-Server installation
    but is it the Server memory or SQL-Server itself ? Do you have
    experience with "Sort Warning" ?
    The bad thing with this issue is that no error message appears in Query
    yser and the results seems to be ok ? Dangerous !






    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Didier Guest

  4. #4

    Default RE: Wrong query result when using join

    Hello Didier,

    Thank you for your reply. According to my experience, ¡°Sort warnings¡±
    usually means that there are no good indexes on the tables. I suggest that
    you use Query yzer to check the difference of execution plans of the
    queries that have and not does not have ¡°Group By¡± clause. Also, does the
    query return the proper results when you saw ¡°Sort warnings¡±?

    First I suggest that you check if you have created index on bookedreserve
    column. Also, I suggest that you use Index Tuning Wizard to tune indexes. I
    have included the following article for your reference:

    311826.KB.EN-US INF: Index Tuning Wizard Best Practices
    http://support.microsoft.com/default.aspx?scid=KB;EN-US;311826

    If you have further questions on the issue, please feel free to post back.

    Thanks & Regards,

    Peter Yang
    MCSE2000, MCSA, MCDBA
    Microsoft Partner Online Support

    Get Secure! - www.microsoft.com/security

    ================================================== ===
    When responding to posts, please "Reply to Group" via
    your newsreader so that others may learn and benefit
    from your issue.
    ================================================== ===
    This posting is provided "AS IS" with no warranties, and confers no rights.

    --------------------
    | From: Didier Thomas <com>
    | References: <phx.gbl>
    | X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
    | Subject: RE: Wrong query result when using join
    | Mime-Version: 1.0
    | Content-Type: text/plain; cht="us-ascii"
    | Content-Transfer-Encoding: 7bit
    | Message-ID: <uy$phx.gbl>
    | Newsgroups: microsoft.public.sqlserver.programming
    | Date: Wed, 16 Jul 2003 03:29:02 -0700
    | NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
    | Lines: 1
    | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP12.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:373971
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    |
    |
    | Hi Peter,
    |
    | thanks for your interest. Actually I could step further in my
    | investigations.
    |
    | The tables I'm trying to compare with join have both the same size i.e
    | each about 1'000'000 records !
    | Therefore I wanted to extract some data out of it to give you some stuff
    | to try out.
    | When I tried to reproduce the problem with this portion It didn't happen
    | again i.e the result contained all expected records.
    |
    | I ran it again for the whole data with SQL Profiler open and.. I had a
    | "Sort Warning".
    | When I ran the query again without "order by" I got no "Sort Warning"
    | and the result is right.
    | So the problem has to do with limitations of our SQL-Server installation
    | but is it the Server memory or SQL-Server itself ? Do you have
    | experience with "Sort Warning" ?
    | The bad thing with this issue is that no error message appears in Query
    | yser and the results seems to be ok ? Dangerous !
    |
    |
    |
    |
    |
    |
    | *** Sent via Developersdex http://www.developersdex.com ***
    | Don't just participate in USENET...get rewarded for it!
    |

    Peter Guest

Similar Threads

  1. #40247 [NEW]: array_merge + array_search gives wrong result
    By y1ngf4n at hotmail dot com in forum PHP Bugs
    Replies: 1
    Last Post: January 26th, 10:14 AM
  2. Inner Join Query
    By KimMazz in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 13th, 05:19 PM
  3. #26255 [Opn->Bgs]: strrpos gives wrong result
    By iliaa@php.net in forum PHP Development
    Replies: 0
    Last Post: November 15th, 06:35 PM
  4. #26255 [NEW]: strrpos gives wrong result
    By hongnk at hotmail dot com in forum PHP Development
    Replies: 0
    Last Post: November 14th, 08:48 PM
  5. SQL join query help
    By poff in forum PHP Development
    Replies: 2
    Last Post: July 13th, 02:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139