Below is a query that I'm trying to create a view with. When I run it, "I get Error Code: 1351. View's SELECT contains a variable or parameter". WHy is this the case and is there a way around this that I can create a view without changing too much of what I have in my current query?
Thanks!!
create view delta as
select rnk2.`date`,
case when rnk1.r1=1 and rnk2.r2=1 then rnk1.X else rnk2.X-rnk1.X end as 'Daily Total'
from (
select `date`,X,@r1:=@r1+1 as r1
from samples, (select @r1:=0) a
order by `date` ) rnk1
inner join
(select `date`,X,@r2:=@r2+1 as r2
from samples, (select @r2:=0) b
order by `date`) rnk2
on (rnk1.r1=1 and rnk2.r2=1) or (rnk1.r1+1=rnk2.r2)
order by rnk2.`date`;
mySQL views does not allow user variables nor subqueries, so I changed the query and split the view into two parts.
The first view will assign a row number on the table SAMPLE according to date. Then the 2nd view will make use of the first view (sample_vw) to do the main query.
create view sample_vw as
select a.`date`, a.X, count(*) as rn
FROM samples a
JOIN samples b
ON a.`date` >= b.`date`
GROUP BY a.`date`;
create view delta as
SELECT t1.`date`,
case when t1.rn=1 and t2.rn=1 then t1.X else t2.X-t1.X end as 'Daily Total'
FROM sample_vw t1
INNER JOIN sample_vw t2
ON t1.rn+1=t2.rn or (t1.rn=1 and t2.rn=1);