mariadb fonction, procedure error You have an error in your SQL

Question

DELIMITER /
CREATE FUNCTION filltimeDim ()
BEGIN
    DECLARE vQuarter VARCHAR(6);
    DECLARE vMonth_Name VARCHAR(20);
    DECLARE vdate_id date;
    DECLARE vyear_id CHAR(4);
    DECLARE vweekly_Name VARCHAR(20);
    DECLARE vMonth_Num TINYINT(10);
    DECLARE vweekday_Num TINYINT(10);
    BEGIN
        SET vdate_id =  CONVERT('1998-01-01', DATE);
        WHILE (CONVERT('vdate_id' USING utf8) <=  '2002-12-31')
        LOOP
            SET vyear_id =  YEAR(vdate_id);
            SET vQuarter =  QUARTER(vdate_id);
            SET vMonth_Name =  MONTHNAME(vdate_id);
            SET vweekly_Name =  DAYOFWEEK(vdate_id);
            SET vMonth_Num =  MONTH(vdate_id);
            SET vweekday_Num =  WEEKDAY(vdate_id);
            INSERT INTO Time_Dim VALUES 
            (vdate_id, vyear_id, vquarter, vMonth_Name,vweekly_Name,vMonth_Num,vweekday_Num);
            SET vdate_id = vdate_id + 1;
        END LOOP;   
    END;
END; /

Error: ERROR 1064 (42000): 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 'BEGIN DECLARE vQuarter VARCHAR(6); DECLARE vMonth_Name VARCHAR(20); ' at line 2

You are explicitly declaring a function. It's expecting the RETURN declaration before the BEGIN. Take a look at the documentation: https://mariadb.com/kb/en/library/create-function/ — powerPixie, Dec 20 '19 at 13:21
Do you really need the `CONVERTs`? The datatype DATE and the strings you have should be compatible. — Rick James, Dec 22 '19 at 05:35
And `SET vdate_id = vdate_id + 1;` won't work with a `DATE`. Instead do `SET vdate_id = vdate_id + INTERVAL 1 DAY;` — Rick James, Dec 22 '19 at 05:36
A "time_dim" dimension table is usually a bad idea for Data Warehousing, at least when you _start_ with the dim table to do a lookup (such as find all "monday" rows). — Rick James, Dec 22 '19 at 05:38
Consider using a "sequence table"; this can avoid the cursor, etc. — Rick James, Dec 22 '19 at 05:39

markusjm · Answer 1 · 2019-12-21T05:26:50.410

You probably want to create a stored procedure instead of a function.

Also, MariaDB uses the DO keyword instead of LOOP to mark the start and end of the WHILE loop. Here's a fixed version of the SQL that completes successfully:

DELIMITER //
CREATE PROCEDURE filltimeDim ()
BEGIN
    DECLARE vQuarter VARCHAR(6);
    DECLARE vMonth_Name VARCHAR(20);
    DECLARE vdate_id date;
    DECLARE vyear_id CHAR(4);
    DECLARE vweekly_Name VARCHAR(20);
    DECLARE vMonth_Num TINYINT(10);
    DECLARE vweekday_Num TINYINT(10);
    BEGIN
        SET vdate_id =  CONVERT('1998-01-01', DATE);
        WHILE (CONVERT(vdate_id USING utf8) <=  '2002-12-31')
        DO
            SET vyear_id =  YEAR(vdate_id);
            SET vQuarter =  QUARTER(vdate_id);
            SET vMonth_Name =  MONTHNAME(vdate_id);
            SET vweekly_Name =  DAYOFWEEK(vdate_id);
            SET vMonth_Num =  MONTH(vdate_id);
            SET vweekday_Num =  WEEKDAY(vdate_id);
            INSERT INTO Time_Dim VALUES
            (vdate_id, vyear_id, vquarter, vMonth_Name,vweekly_Name,vMonth_Num,vweekday_Num);
            SET vdate_id = vdate_id + INTERVAL 1 DAY;
        END WHILE;
    END;
END; //

Note that the modified SQL uses date arithmetic instead of numeric arithmetic. This correctly updates the date whereas the original caused a date overflow.

Thanks! Oops! I came late. Anyway, after spending some time on MariaDB documentation, I managed to do it and made it work, and yes I needed a procedure. I didn't quite understand date arithmetic and numeric arithmetic? — comocoder, Dec 22 '19 at 00:14
If you are using dates as the data type, you must increment them with `+ INTERVAL 1 DAY`, otherwise you'll get errors. — markusjm, Dec 23 '19 at 09:55

mariadb fonction, procedure error You have an error in your SQL

1 Answers1

Linked