webMac
webMac

Reputation: 203

Is it possible to use a variable or parameter in a MySQL view?

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

Answers (2)

walter_white
walter_white

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

Gordon Linoff
Gordon Linoff

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

Related Questions