MySQL - Cannot Create View with SET variable inside

Question

I am trying to create a view with SET @rank = 0; inside but it's giving me errors. Been trying different things but it's not working. Can anyone please point me to the right direction?

CREATE VIEW S1_Bottom_Performer_AHT as (
SET @rank=0
SELECT @rank := @rank+1 AS '#',
                ei.SM,
                ei.TM,
                es.Month_Date,
                ei.emp_id,
                ei.DNAME,
                ei.STATUS,
                ei.SHIFT,
                ei.SKILL,
                ei.HIRE_DATE,
                ifnull(TIMESTAMPDIFF(MONTH, ei.HIRE_DATE, now()), '-') AS Tenure,
                ifnull(es.Call_Handled, '-') AS Call_Handled,
                ifnull(es.AHT, '-') AS AHT
FROM mtl_extended_info ei
LEFT OUTER JOIN
  ( SELECT es.Employee_ID,
           es.Month_Date,
           sum(es.Calls_Handled_Ct) AS Call_Handled,
           round((sum(es.I_Talk_Time_Sec) + sum(es.Hold_Time_Sec) + sum(es.I_Work_Time_Sec) + sum(es.I_AUX_Out_Time_Sec)) / sum(es.Calls_Handled_Ct)) AS AHT
   FROM cdl_agent_call_voume_gen es
   WHERE es.Month_Date = '2013-09-01'
   GROUP BY es.Employee_ID,
            es.Month_Date ) es ON es.Employee_ID = ei.emp_id
WHERE es.Month_Date = '2013-09-01'
  AND ei.Visible = 1
  AND ei.SKILL != 'RSD'
GROUP BY ei.emp_id
ORDER BY es.AHT DESC LIMIT 80);

Error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @rank=0
SELECT @rank := @rank+1 AS '#',
                ei.SM,
          ' at line 2

What is your mysql version? I 've tried to create a view which contains select statement with @variables, but it says `View's SELECT contains a variable or parameter` — Channa Jayamuni, Jan 06 '17 at 06:30

score 9 · Answer 1 · answered Sep 26 '13 at 17:53

9

I think you cannot do that.

As from the MYSQL guidelines:

A view definition is subject to the following restrictions:

[ deletia ]

The SELECT statement cannot refer to system or user variables.

answered Sep 26 '13 at 17:53

Rahul Tripathi

168,305
31
280
331

2

Is there an alternative to VIEW? – John Guan Sep 26 '13 at 17:58

score 5 · Answer 2 · edited May 23 '17 at 12:33

Views are select statements, nothing more. Views can not be multiple statements. If you can't get this view down to a single statement, try the suggestion here to use a function or procedure.

Try this suggestion for MYSQL using a join instead of a set.

JOIN    (SELECT @rank:= 0) r;

Here is a untested example, I am not sure if you can order by '#' at the end, but if you can it would sort everything correctly by rank:

CREATE VIEW S1_Bottom_Performer_AHT as (
SELECT @rank := @rank+1 AS '#', *
FROM
(SELECT         ei.SM,
                ei.TM,
                es.Month_Date,
                ei.emp_id,
                ei.DNAME,
                ei.STATUS,
                ei.SHIFT,
                ei.SKILL,
                ei.HIRE_DATE,
                ifnull(TIMESTAMPDIFF(MONTH, ei.HIRE_DATE, now()), '-') AS Tenure,
                ifnull(es.Call_Handled, '-') AS Call_Handled,
                ifnull(es.AHT, '-') AS AHT
FROM mtl_extended_info ei
LEFT OUTER JOIN
  ( SELECT es.Employee_ID,
           es.Month_Date,
           sum(es.Calls_Handled_Ct) AS Call_Handled,
           round((sum(es.I_Talk_Time_Sec) + sum(es.Hold_Time_Sec) + sum(es.I_Work_Time_Sec) + sum(es.I_AUX_Out_Time_Sec)) / sum(es.Calls_Handled_Ct)) AS AHT
   FROM cdl_agent_call_voume_gen es
   WHERE es.Month_Date = '2013-09-01'
   GROUP BY es.Employee_ID,
            es.Month_Date ) es ON es.Employee_ID = ei.emp_id
WHERE es.Month_Date = '2013-09-01'
  AND ei.Visible = 1
  AND ei.SKILL != 'RSD'
GROUP BY ei.emp_id
ORDER BY es.AHT DESC LIMIT 80)
) AS RESULTS
JOIN    (SELECT @rank:= 0) r; 
ORDER BY '#'

I tried that but it's not giving me the number in order. it starts at 58 and then 5, 8 etc... — John Guan, Sep 26 '13 at 18:31
Do note that you have GROUP BY ei.emp_id ORDER BY es.AHT DESC LIMIT 80); This can mess up your rank ordering. I might try making your current select a sub query, taking the rank out of it, then making an outer select that selects from your current query and adds on the rank to your final result set. — Vulcronos, Sep 26 '13 at 18:40
@ChongGuan Edited my answer. I can't test it but hopefully it is close enough to give you a start. — Vulcronos, Sep 27 '13 at 15:34
It's giving me the follow error: `#1351 - View's SELECT contains a variable or parameter` — John Guan, Sep 27 '13 at 21:35
Which version of mysql allows to create a view with variables? — Channa Jayamuni, Jan 06 '17 at 05:56

MySQL - Cannot Create View with SET variable inside

2 Answers2

Linked

Related