Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Same Data Set Different Results

    I am transferring an Access DB to SQL Server and running a simple SELECT statement, but am getting differeing results when I run it in SQL than when its run through Access. They use the same table to extract from.

    Have I been daft and missed something off or why would this happen?

    Access Code
    Code:
    SELECT PARAMED_temp.ContactServiceID, Count(PARAMED_temp.EventID) AS CountOfEventID
    FROM PARAMED_temp
    WHERE (((PARAMED_temp.F2F)="y") 
    AND ((PARAMED_temp.TH_SOR_LOC_CAT)="DA" 
    Or (PARAMED_temp.TH_SOR_LOC_CAT)="NR" 
    Or (PARAMED_temp.TH_SOR_LOC_CAT)="COM"))
    GROUP BY PARAMED_temp.ContactServiceID;
    SQL Server Code
    Code:
    SELECT     ContactServiceID, COUNT(EventID) AS CountOfEventID
    FROM         jez.PARAMED_TEMP_BUILD
    WHERE     (F2F = 'y') AND (TH_SOR_LOC_CAT = 'DA' OR
                          TH_SOR_LOC_CAT = 'NR' OR
                          TH_SOR_LOC_CAT = 'COM')
    GROUP BY ContactServiceID

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The queries look logically identical to me.
    What are the different results you are seeing?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - I can't think of any reason other than data.

    Either return any NULLS for EventID?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Jet (MS-Access) query you posted isn't syntactically correct. The parens don't match, you have more open than close parens.

    Please post the query that you are actually using.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Count again, Pat.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hmmm... I must have mis-copied the query. It looks correct now.

    My next guess would be a collation issue, possibly case sensitivity. To test for this, run the query:
    Code:
    SELECT COUNT(*), F2F
       FROM jez.PARAMED_TEMP_BUILD
       GROUP BY F2F
    
    SELECT COUNT(*), TH_SOR_LOC_CAT
       FROM jez.PARAMED_TEMP_BUILD
       GROUP BY TH_SOR_LOC_CAT
    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Case sensitivity is what I was leaning towards.
    I think a collation issue would throw an error.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I see case sensitivity as being a collation issue.

    -PatP

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    It was the wording of some of my Uodates I had run earlier.

    Thanks

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So it WAS a data issue!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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