Hi we are migrating postgres to Mysql, and there is some serious issues with Subqueries. Can you please help me out in this query ??
SELECT
COUNT(X.jmodule) as total,
SUM(CASE WHEN X.result = ‘PASS’ THEN 1 ELSE 0 END) as pass,
SUM(CASE WHEN X.result <> ‘PASS’ THEN 1 ELSE 0 END) as fail,
SUM(CASE WHEN X.result <> ‘PASS’ AND X.jmodule
NOT IN (
SELECT Y.jmodule
FROM
(SELECT jmodule,result,opid,fid,date FROM e6lmc UNION ALL SELECT jmodule,result,opid,fid,date FROM e6op125 UNION ALL SELECT jmodule,result,opid,fid,date FROM e6op150) Y WHERE Y.jmodule = X.jmodule AND Y.date > X.date ) THEN 1 ELSE 0 END ) as hard,
count(distinct (CASE WHEN X.result = ‘PASS’ THEN X.jmodule END)) as outp,
SUBSTRING(RTRIM(jmodule),6,7) AS modas,
result,
opid,
fid
FROM (
SELECT jmodule,result,opid,fid,date,mrn FROM e6lmc
UNION ALL
SELECT jmodule,result,opid,fid,date,mrn FROM e6op125
UNION ALL
SELECT jmodule,result,opid,fid,date,mrn FROM e6op150) X
WHERE
X.date > ‘2014/08/28 05:00:00’ AND X.date < ‘2014/08/28 13:00:00’ AND X.jmodule IS NOT NULL
group by
modas,result,opid,fid
order by
total desc
Thanks