#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    245
    Rep Power
    16

    Oracle vs MYSQL - different row number returned


    Hi,

    Using this query in both mysql and Oracle, the Oracle DB is returning the correct number of rows (41) but MYSQL only returns 14. The tables selected below have the same row count and data in each database.
    I am guessing something at a high level is wrong with the mysql statement:

    Code:
    SELECT DISTINCT(OPR.FINCLASS) FROM OPRFIN as OPR 
      , USER as RU 
      , CLASS as RC 
      , ITEM as AI 
      , MENUITEM as MI 
      , TAG as B 
      , GROUP as PG 
     WHERE OPR.ACCTLOCK = 0 
       AND B.DISPLAY_ONLY = 0 
       AND B.TAG_CLASS = 'S' 
       AND B.TAG_TYPE = 'C' 
       AND RU.RU1 = OPR.FINCLASS 
       AND RU.RN = RC.RN 
       AND RC.CLASSID = AI.CLASSID 
       AND AI.BARITEMNAME = MI.ITEMNAME 
       AND AI.MENUNAME = MI.MENUNAME 
       AND AI.BARNAME = MI.BARNAME 
       AND B.MENU_ID = 'DEFAULT-RL'  
       AND B.PNLNAME = PG.PNLNAME 
       AND PG.ITEMNAME = AI.PNLITEMNAME 
       AND B.GRPNAME = MI.GRPNAME 
       AND MI.GRPNAME = PG.GRPNAME 
       AND B.DISPLAY_ONLY = AI.DISPLAYONLY 
       AND ((B.MATCH_FLG = 'S' 
       AND AI. ACTIONS <= B. ACTIONS) 
        OR (B.MATCH_FLG = 'A' 
       AND B. ACTIONS = AI. ACTIONS))
      AND B.MENUFOODID = '1'
    Any thoughts on what I am missing?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,792
    Rep Power
    4331
    Originally Posted by genista
    The tables selected below have the same row count and data in each database.
    i'd put money on the tables ~not~ having the same data in the two databases

    p.s. do yourself a favour, learn explicit joins, e.g. INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    Does that "GROUP as PG" not cause an error? Due to "group" being a keyword.

    If it's established that the data really is identical, maybe there's some difference in how Oracle/MySQL is enforcing the "DISTINCT" criteria. That's just a wild guess.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,792
    Rep Power
    4331
    Originally Posted by Lobe
    ... maybe there's some difference in how Oracle/MySQL is enforcing the "DISTINCT" criteria. That's just a wild guess.
    no, both interpret it the same... and both even ignore the parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    245
    Rep Power
    16
    The data is identical, as Oracle is the source I cleared the MYSQL tables and re-imported everything. As another note, if I remove the distinct I get records show up that were not there when I was using DISTINCT (as a result of sifting through the hundreds of results that get returned now). These values are there in the Oracle result.
    Last edited by genista; April 3rd, 2018 at 01:00 AM.

IMN logo majestic logo threadwatch logo seochat tools logo