A few tricky queries to migrate

[David Wynter]

Moving an application from SQL server to MySQL. There are a few queries I cannot get my head around. Any help appreciated.

INSERT INTO EQ_ISSUER_INFORMATION
SELECT fii.ISSUER_ORGID,
RTRIM(SUBSTRING(IFNULL(IFNULL(fii.ISSUER_SHORT_NAME, fii.ISSUER_LONG_NAME), 'NO NAME, TICKER: ' + fii.ISSUER_TICKER ),1,80) ),
RTRIM(SUBSTRING(IFNULL(IFNULL(fii.ISSUER_SHORT_NAME, fii.ISSUER_LONG_NAME), 'NO NAME, TICKER: ' + fii.ISSUER_TICKER ),1,16) ),
fii.ISSUER_CNTRY_CD, NOW()
FROM (
SELECT ISSUER_ID = MAX(fi.ISSUER_ID), fi.ISSUER_ORGID
FROM FI_ISSUER_INFORMATION fi
WHERE fi.ISSUER_ORGID IS NOT NULL AND NOT EXISTS (SELECT *
FROM EQ_ISSUER_INFORMATION eq
WHERE eq.Issuer_ID = fi.ISSUER_ORGID)
GROUP BY fi.ISSUER_ORGID
) TBL INNER JOIN FI_ISSUER_INFORMATION fii ON TBL.ISSUER_ID = fii.ISSUER_ID

doesn't like the INNER JOIN on the derived table, nor if I use a WHERE clause

This one is more involved, have changed some of the terms

UPDATE SECM_SECURITY_UNIVERSE
SET EQU_RIC = a.RIC,
SECURITY_TYPE = a.TYPE,
NAME = a.DESCR,
CUSIP = a.CUSIP,
SEDOL = a.SEDOL,
ISIN= a.ISIN,
VALOREN= a.VALOREN,
PRIMARY_TICKER= a.TICKER,
ASSET_TYPE = a.ASSET_TYPE,
ACTIVE_CODE = a.ACT_CD,
STAMP = a.G_DATE,
CREATED_BY = a.RWT,
UL_AIM_SECURITY_ID = a.UND_ID
FROM
( SELECT DISTINCT e2.RIC, 52 AS TYPE,
UPPER(COALESCE(e2.description, e2.Directory_Company_Name + ' ADR', e2.Organisation_Display_Name + ' ADR'))
+ ' ON ' + dd.CODE_NAME AS DESCR,
e2.CUSIP, e2.SEDOL, e2.ISIN, e2.VALOREN, e2.TICKER,
2 AS ASSET_TYPE, 1 AS ACT_CD, NOW() AS G_DATE, 'rwt' AS RWT,
e2.PRIMARY_RIC_IDENTIFIER, u_su.SECURITY_ID AS UND_ID,
CASE
WHEN u_su.SECURITY_ID = su.SECURITY_ID THEN NULL
ELSE su.SECURITY_ID
END AS SEC_ID

FROM EQUITIES_XREF e1
INNER JOIN SECM_SECURITY_UNIVERSE su
ON su.EQU_RIC = e1.RIC
LEFT OUTER JOIN EQUITIES_XREF e2
ON e1.PRIMARY_ISSUE_LEVEL_CODE = e2.PRIMARY_ISSUE_LEVEL_CODE
AND e2.RIC NOT LIKE '%^%'
AND e2.PRIMARY_RIC_IDENTIFIER NOT LIKE '%^%'
AND e2.ISSUE_CLASSIFICATION = 'DR'
LEFT OUTER JOIN EQ_DOMAIN_VALUES dd
ON dd.CODE_NAME = e2.EXCHANGE_ID AND dd.DOMAIN_CODE = 'TE'
LEFT OUTER JOIN SECM_SECURITY_UNIVERSE u_su
ON u_su.EQU_RIC = e2.PRIMARY_RIC_IDENTIFIER
WHERE su.EQU_RIC = e2.RIC
AND asu.ASSET_ID IS NULL AND e2.PROCESSED =1 AND u_su.AIM_SECURITY_ID IS NOT NULL
) a WHERE SECM_SECURITY_UNIVERSE.SECURITY_ID = a.SEC_ID
AND SECM_SECURITY_UNIVERSE.ASSET_ID IS NULL

Again it does not like the derived table for some reason.

Thx

David