[genista]

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:

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?

[r937]

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

[Lobe]

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.

[r937]

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

[genista]

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.