Not the answer you need?
Register and ask your own question!

NOT IN, Subquery Opmisation Help PLZZZ

sreedhardevireddysreedhardevireddy EntrantInactive User Role Beginner
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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.