Reputation: 203
I'm trying to create a View as following:
CREATE VIEW v_MyView
AS
SET @par_count := 0; -- XXX Working in SELECT, but not in a View !? XXX
SELECT
q1.day,
q1.count_per_day,
(@par_count := @par_count + q1.count_per_day) AS count_sum -- sums up count_per_day
FROM
(SELECT
DATE(registration_date_time_obj) AS day,
COUNT(Date(registration_date_time_obj)) AS count_per_day
FROM tbl_registration_data
GROUP BY day
ORDER BY day
) AS q1
;
The select statement itself works fine, just creating a view fails in MySQL since it doesn't accept user variables/parameters within it's declaration i guess.
Is there a way to still create this view with a workaround for the parameter?
Anyways, i'm able to create a similar procedure for the select statement, but that doesn't really solve the problem since i can't call the procedure in another select statement...
Thanks for your suggestions and solutions! (:
Upvotes: 4
Views: 14329
Reputation: 76
Please read the documentation on VIEWS >> https://dev.mysql.com/doc/refman/5.5/en/create-view.html
A view definition is subject to the following restrictions:
The SELECT statement cannot contain a subquery in the FROM clause.
The SELECT statement cannot refer to system variables or user-defined variables.
Within a stored program, the SELECT statement cannot refer to program parameters or local variables.
The SELECT statement cannot refer to prepared statement parameters.
Any table or view referred to in the definition must exist. After the view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.
The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
You cannot associate a trigger with a view.
Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).
Upvotes: 2
Reputation: 1269443
MySQL documentation is pretty clear that variables are not allowed:
The SELECT statement cannot refer to system variables or user-defined variables.
Within a stored program, the SELECT statement cannot refer to program parameters or local variables.
You can do what you want using a correlated subquery:
SELECT DATE(registration_date_time_obj) AS day,
COUNT(Date(registration_date_time_obj)) AS count_per_day,
(SELECT COUNT(*)
FROM tbl_registration_data rd2
WHERE rd2.registration_date_time_obj <= date_add(date(rd.registration_date_time_obj), interval 1 day)
FROM tbl_registration_data rd
GROUP BY day
ORDER BY day;
Upvotes: 3