April 2nd, 2018, 02:07 AM
-
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?
April 2nd, 2018, 05:06 AM
-
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
April 2nd, 2018, 05:38 PM
-
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.
April 2nd, 2018, 06:42 PM
-
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
April 3rd, 2018, 01:37 AM
-
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 02:00 AM.