MySQL Forums
Forum List  »  Views

View's SELECT contains a variable or parameter
Posted by: Emre Sen
Date: January 24, 2014 07:58PM

hi,
i have problem by creating a view.
at first, here is an example table:

----------------------------
|userId|timestamp|...|limit|
----------------------------
----------------------------
| 1 |111231111|...| 3 |
----------------------------
| 1 |114411112|...| 3 |
----------------------------
| 1 |112111113|...| 3 |
----------------------------
| 1 |111211114|...| 3 |
----------------------------
| 2 |111111115|...| 1 |
----------------------------
| 2 |111131117|...| 1 |
----------------------------

The userId and the timestamp is a clustered index.
I need a limit on results which has the same userId.
I found a solution by using a variable.

Here is an example:
"SELECT @row_num := IF(@prev_value=o.Customer,@row_num+1,1) AS RowNumber
,o.Customer
,o.OrderDate
,o.Amount
,@prev_value := o.Customer
FROM Orders o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY o.Customer, o.OrderDate DESC"
Example results:
--------------------
|RowNumber|Customer|
--------------------
--------------------
| 1 |John |
--------------------
| 2 |John |
--------------------
| 3 |John |
--------------------
| 1 |Mark |
--------------------
| 2 |Mark |
--------------------


I can set a WHERE clause in my example and i have limit by userId.
"WHERE rowNumber <= limit"

But i got the following error: "View's SELECT contains a variable or parameter"

my question is:
is there another solution to set a limit on userId
or is there a way to use variables in views?

Options: ReplyQuote


Subject
Views
Written By
Posted
View's SELECT contains a variable or parameter
13525
January 24, 2014 07:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.