1

I have a function like this:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

It works as well. Now I want to also pass another parameter to the function to determine the time range. I mean I want to pass a string instead of WEEK. Here is my new function:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT, range VARCHAR(10)) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

But it throws this error:

The following query has failed: "CREATE DEFINER=root@localhost FUNCTION user_repo(user_id INT, range VARCHAR(10) CHARSET utf8) RETURNS VARCHAR(20) CHARSET utf8mb4 NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE vote_value mediumint; DECLARE score mediumint; SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0) INTO vote_value, score FROM reputations WHERE owner_id = user_id AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range)); RETURN CONCAT_WS(',', vote_value, score); END" MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'range)); RETURN CONCAT_WS(',', vote_value, score); END' at line 9

Does anybody what's wrong?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Martin AJ
  • 6,261
  • 8
  • 53
  • 111
  • 1
    `RANGE` is reserved word in MySql. Use another name like `range1` or `my_range` for this parameter. You can also use backtics around this name, see this answer: https://stackoverflow.com/questions/261455/using-backticks-around-field-names – krokodilko Jul 23 '17 at 06:53
  • @krokodilko I was suspected about the point you mentione before asking, but that's not the problem. The error is still there. – Martin AJ Jul 23 '17 at 06:59
  • @Shadow why did you marked my question as duplicate? My question is a bit different. How can I use `CASE WHEN` in the `WHERE` clause? – Martin AJ Jul 23 '17 at 07:01
  • Nope, your problem is exactly the same: you would like to supply the unit parameter of the date_sub() function via a variable. It is a no go. The solutions are demonstrated by the duplicate topic. The entire sql statement must be in the various branches, not just the where part, or use the prepared statement solution demonstrated in the question itself. – Shadow Jul 23 '17 at 07:03
  • @Shadow I think you're right, but when I try it, it throws syntax error, may you please show me how exactly? – Martin AJ Jul 23 '17 at 07:09
  • @Martin AJ please see this [fiddle](http://sqlfiddle.com/#!9/5715c3) - it compiles fine with `range1`. If you replace `range1` with `range`, it breaks. – krokodilko Jul 23 '17 at 07:10
  • @krokodilko I don't see neither `range` nor `range1` in your fiddle. – Martin AJ Jul 23 '17 at 07:11
  • @Martin AJ Then use `Ctrl-F` in your browser, type `range` in, and hit Enter - this will search a word `range` on the page. There is `range1` word in the header of the function. If you click on `Build Schema` button, it compiles fine. If you replace `range1` with `range` and click `Build Schema` button again, you wll see an error. – krokodilko Jul 23 '17 at 07:15
  • @krokodilko Ah my bad. Yes `range1` is fine and `range` doesn't work. But you know, the problem is a different thing. I want to use `range1` in the `DATE_SUB` function. So please replace `range1` with `WEEK` in your fiddle. That's the problem .. – Martin AJ Jul 23 '17 at 07:18
  • @Shadow Still I stuck on it https://gist.github.com/anonymous/d01cbd97cc20ad1882db316273b2d6d6 ...! Please reopen my question and let people help me. – Martin AJ Jul 23 '17 at 07:31

0 Answers0