Announcement

Announcement Module
Collapse
No announcement yet.

NOT IN, Subquery Opmisation Help PLZZZ

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • NOT IN, Subquery Opmisation Help PLZZZ

    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
    Last edited by sreedhardevireddy; 08-28-2014, 10:17 AM.
Working...
X