sub query
Hi
We are currently evaluating a migration of data from MsSql to MySql.
I've copied the tables and indexes across and the data is all fine. However, I have a subquery which returns data on MSSQL, but no data on MySQL
the subquery below refers to ordersh.orddate, which is in the main query. When I alter that to a physical date (stored as integer 20080931 etc), the query runs (which isn't helpful to what i'm trying to achieve!).
SELECT DISTINCT ORDERSH.ORDHID
FROM ORDERSD
INNER JOIN ORDERSH ON ORDERSD.ORDHID = ORDERSH.ORDHID
INNER JOIN CUSTOMERMAILINGLIST on ORDERSH.ORDCUSTOMER = CUSTOMERMAILINGLIST.CUSTID
WHERE ORDERSD.ITEMSTATUS = 5 AND (ORDERSH.ORDSOURCE = 'website') and CUSTOMERMAILINGLIST.LISTID IN
(select CATALOGUESENT.MAILINGLISTID
from CATALOGUESENT
where (CATALOGUESENT.CATALOGUEID = 29)
And (CATALOGUESENT.ISADDITIONALLIST = 0)
and ORDERSH.ORDDATE > CATALOGUESENT.DATESENT)
"explain" gives the following output
1 | PRIMARY | ORDERSD | ref | IX_WAREHOSUESALECOUNT,Index_5,pickditem,Index_8,HID,Index_10 | Index_10 | 5 | const | 160082 | Using where | Using temporary
1 | PRIMARY | ORDERSH | eq_ref | PRIMARY,Index_4,Index_5,Index_6 | PRIMARY | 4 | stilettoxt.ORDERSD.ORDHID | 1 | Using where
1 | PRIMARY | CUSTOMERMAILINGLIST | ref | CUSTID_LISTID_IX,CUSTID_IX,Index_4 | CUSTID_LISTID_IX | 5 | stilettoxt.ORDERSH.ORDCUSTOMER | 1 | Using where | Using index | Distinct
2 | DEPENDENT SUBQUERY | CATALOGUESENT | index_subquery | Index_1,Index_2,Index_3 | Index_2 | 15 | func,const,const | 1 | Using where
Any ideas?
Subject
Written By
Posted
sub query
September 23, 2008 10:12AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.