#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    4
    Rep Power
    0

    Stored Procedure w concat(date) returning error


    Hi All - New to MySQL so apologies in advance!

    Need help with a stored procedure that uses the curdate() function to dynamically create a table name like table_2013-09-03.

    When I call the SP I get the following error:

    Error Code: 1064. You have an error in your SQL syntax;
    check the manual that corresponds to your MySQL server version
    for the right syntax to use near 'null GROUP BY date(full_date)
    ORDER BY date(full_date)' at line 7


    Here is the full stored procedure:

    DELIMITER $$

    CREATE DEFINER=`bi`@`10.%.%.%` PROCEDURE `blimp`()
    BEGIN

    Declare sql_text varchar(500);
    Declare table_name varchar(100);
    Declare currentdate datetime;

    Set @table_name := '';

    SET @table_name := concat('bibluedb.BLIMP_RegCount_' , @currentdate);

    SET sql_text = concat("create table " , @table_name ,
    " as select YYYYWW, Full_Date as Date, count(MasterAccountID) as Registration_Count
    from bibluedb.dim_day a left join bibluedb.account b
    on a.full_date = date(b.createdDate)
    where full_date > date_sub(curdate(), interval 30 day)
    and full_date > '2013-07-01'
    and full_date < curdate()
    and full_date not null
    GROUP BY date(full_date)
    ORDER BY date(full_date)");

    Set @dynamicSQL = sql_text;

    PREPARE stmt FROM @dynamicSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    END

    I assume I have a problem with nulls? The full_date column does not specify 'NOT NULL'. Full_Date comes from a date dimension where full_date is a datetime field (not a key) that looks like 2013-09-03. If I 'select * from dim_date where full_date is null, I get zero records.

    Thanks in advance!
    John
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,945
    Rep Power
    4033
    your condition
    Code:
    and full_date not null
    should be
    Code:
    and full_date IS not null
    .

    Why are you trying to create a table named for the current date? That reeks of bad design and nothing but trouble to come.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by kicken
    your condition
    Code:
    and full_date not null
    should be
    Code:
    and full_date IS not null
    .

    Why are you trying to create a table named for the current date? That reeks of bad design and nothing but trouble to come.
    Thanks.. that ran. But, I forgot to make the date dynamic.

    Here's the relevant portion:

    Declare currentdate datetime;

    Set @table_name := '';

    Set @currentdate := curdate();

    SET @table_name := concat('bibluedb.BLIMP_RegCount_' , @currentdate);

    SET sql_text = concat("create table " , @table_name ,

    -- Why the dynamic filename? Hopefully won't have to go there but it was brought up as an option. Data will get pushed into a csv file and exposed on a portal for download by client. That will be another thread!

    I would like to learn how to concatenate a date into a variable though - table name or file name.

    Thanks again...

IMN logo majestic logo threadwatch logo seochat tools logo