SQL Filter based on case statement-CodePudding

I have some logic that is close to the example below:

SELECT
    employee.num,
    CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
    END AS attr_status

FROM employee

LEFT JOIN job ON employee.key = job.key

LEFT JOIN physical ON physical.key = job.key

LEFT JOIN chemical ON chemical.key = job.key

WHERE attr_status = 'A'

Any suggestions on how to achieve this?

Thank you.

CodePudding user response：

Because in most (or all ?) DBMS system you can not use an alias in the the WHERE-clause, you have 2 options:

Replace attr_status in the WHERE-clause with a copy of the CASE

SELECT
    employee.num,
    CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
    END AS attr_status

FROM employee

LEFT JOIN job ON employee.key = job.key

LEFT JOIN physical ON physical.key = job.key

LEFT JOIN chemical ON chemical.key = job.key

WHERE CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
      END = 'A'

Make your query a sub-query:

SELECT * 
FROM (
   SELECT
       employee.num,
       CASE job.type
           WHEN 'C' THEN chemical.chem_status
           ELSE physical.phys_status
       END AS attr_status

   FROM employee

   LEFT JOIN job ON employee.key = job.key

   LEFT JOIN physical ON physical.key = job.key

   LEFT JOIN chemical ON chemical.key = job.key 
) x

WHERE x.attr_status = 'A'

CodePudding user response：

You could do a simple where too

  Where (job.type ='C' and chemical.chem_status='A') 
                or (job.type! ='C' and physical.phys_status='A')