0

In the regular EMP,DEPT table configuration (stock data provided by Oracle), I have the following query to solve:

List the empno,ename,sal,job,deptno & experience of all the emps belongs to dept 10 or 20 with an exp 36 to 40 years working under the same mgr without commission

I came up with the following query:

SELECT 
  e.ENAME as EMPLOYEE
  , m.ENAME AS MANAGER
  , e.SAL, e.COMM, e.JOB, e.DEPTNO
  , (SELECT @exp := ROUND(DATEDIFF(NOW(), e.HIREDATE)/(12 * 30), 1) ) AS EXP
FROM EMP e INNER JOIN EMP m ON e.MGR = m.EMPNO
WHERE e.DEPTNO in (10, 20)
AND (e.COMM IS NULL or e.COMM = 0)
AND @exp BETWEEN 35.0 AND 40.0;

Regardless of whether the query is right/wrong, when I run this query 2 times in a row, the second run (and all subsequent runs) does not produce any result (the first run does)

I looked for user defined variable questions on StackOverflow and only one of them kinda answered but still not what I was looking for. In that post, the author steampowered quotes the MySQL documentation

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.

If that's so, then how do we prevent repeating computations in SELECT and WHERE CLAUSE?

devak23
  • 419
  • 1
  • 4
  • 6
  • why not just repeat the expression in the where clause instead of using user defined vars? – Gurwinder Singh Aug 03 '17 at 03:56
  • Sure.. i tried that and it works... but is there a way to not repeat the same calculation again and again? – devak23 Aug 03 '17 at 04:08
  • You can do calculation in subquery and assign the column an alias. Then in outer query you can filter based on that column. But that is hardly going to help because MySQL materializes subqueries – Gurwinder Singh Aug 03 '17 at 04:10
  • You mean something like this? SELECT e.ENAME, A.EXP FROM EMP e, ( SELECT ROUND(DATEDIFF(NOW(), HIREDATE)/(12 * 30),1) AS EXP, EMPNO FROM EMP ) A WHERE A.EXP BETWEEN 35.0 AND 40.0 AND e.EMPNO = A.EMPNO; – devak23 Aug 03 '17 at 04:18

0 Answers0