I'm trying to convert this query from Oracle to MySQL:
CREATE TABLE SCT_201301_UMLS_SYN
AS SELECT DISTINCT A.SCUI "SNOMED_CODE", A.STR "SNOMED_DISPLAY_NAME"
, B.STR "UMLS_SYNONYM"
FROM MRCONSO A, MRCONSO_UMLS B
WHERE A.SCUI IN (SELECT referencedComponentId
FROM SnomedCTtoICD10)
AND A.SAB='SNOMEDCT'
AND A.SUPPRESS='N'
AND A.TTY='PT'
AND B.LAT='ENG'
AND B.SUPPRESS='N'
AND A.CUI=B.CUI
Any ideas how to do this?
If you are using an older version of MySQL, then you might have a problem with the performance of in. Here is an alternative, which should work in both MySQL and Oracle:
CREATE TABLE SCT_201301_UMLS_SYN
AS SELECT DISTINCT A.SCUI "SNOMED_CODE", A.STR "SNOMED_DISPLAY_NAME"
, B.STR "UMLS_SYNONYM"
FROM MRCONSO A join
MRCONSO_UMLS B
on A.CUI = B.CUI
WHERE exists (SELECT 1
FROM SnomedCTtoICD10 s
where s.referencedComponentId = a.scui)
AND A.SAB='SNOMEDCT'
AND A.SUPPRESS='N'
AND A.TTY='PT'
AND B.LAT='ENG'
AND B.SUPPRESS='N'
Not sure why your statement didn't work, but you could try it using ANSI syntax:
CREATE TABLE SCT_201301_UMLS_SYN
AS
SELECT DISTINCT A.SCUI "SNOMED_CODE", A.STR "SNOMED_DISPLAY_NAME" , B.STR "UMLS_SYNONYM"
FROM MRCONSO A
join MRCONSO_UMLS B
using (CUI)
WHERE A.SCUI IN (SELECT referencedComponentId
FROM SnomedCTtoICD10)
AND A.SAB='SNOMEDCT'
AND A.SUPPRESS='N'
AND A.TTY='PT'
AND B.LAT='ENG'
AND B.SUPPRESS='N'
;
That query should work just fine in MySQL (>=5.1). MySQL should accept that syntax.
But there could be some differences. By default, Oracle is "case sensitive" and MySQL is not. So one difference you might run into is the number of rows eliminated by the DISTINCT keyword. (You could also encounter this difference between Oracle and Oracle, or MySQL and MySQL.)
Obviously, the tables referenced by the SELECT statement need to exist on the local MySQL database, and the user running the query needs to have SELECT privilege on them. (In Oracle, these references might be references to views or to synonyms, rather than tables. And those views and synonyms might reference objects in other schemas.)
MRCONSO
MRCONSO_UMLS
SnomedCTtoICD10
It's not possible to run a query on MySQL to retrieve data from a remote Oracle database. So, for this query to work, those objects will need exist on the local MySQL database.
Oracle people define the way the tables should be joined just by adding more conditions to the 'Where' clause. MySql people define the join within the join statement. So move
AND B.LAT='ENG'
AND B.SUPPRESS='N'
AND A.CUI=B.CUI
to be replaced with
FROM MRCONSO A join MRCONSO_UMLS B
on B.LAT='ENG'
AND B.SUPPRESS='N'
AND A.CUI=B.CUI
