Adwait Chitaley
asked on
Variable in Mysql VIEW
Please peruse the following Query:
SELECT @rownum:=@rownum+1 as rownum, employee_name from (SELECT @rownum:=0) r, employees
This query is used in MySQL to derive ROWNUMBERS (as done in case of Oracle)
My query is that... how can one use this query to create a VIEW in MySQL... when MySQL returns "VARIABLE inside View" error?
Any alternate method of availing ROWNUM in MySQL Views itself?
Thanks in advance.
SELECT @rownum:=@rownum+1 as rownum, employee_name from (SELECT @rownum:=0) r, employees
This query is used in MySQL to derive ROWNUMBERS (as done in case of Oracle)
My query is that... how can one use this query to create a VIEW in MySQL... when MySQL returns "VARIABLE inside View" error?
Any alternate method of availing ROWNUM in MySQL Views itself?
Thanks in advance.
Just throwing this out there... If your view is meant to be read-only, you do it as the return of a stored procedure instead.
you can create the "view" as a table instead of a view and get the #s.
elsewhise, you can't use variables
elsewhise, you can't use variables
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this query. You can create a view using this SQL.
select (select count(*) from employees e2 where e1.employee_name >= e2.employee_name) as rownum,employee_name
from employees e1;
select (select count(*) from employees e2 where e1.employee_name >= e2.employee_name) as rownum,employee_name
from employees e1;