🔒 Locked

[web] SQL - CASE statement ?

Started by NewbieA Oct 29, 2005 at 10:36 AM 2 replies 850+ views

Original Post

Oct 29, 2005 at 10:36 AM

Hello guys, is me got stuck again. [smile] How this 'CASE' thing work? I watch the mysql online doc, then I type in this:


DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`_stat` $$
CREATE PROCEDURE `test`.`_stat` (_start DATE, _interval INT, _type CHAR)
BEGIN
  DECLARE _end DATE;
  DECLARE _test INT DEFAULT 0;

  CASE _type
    WHEN _type = 'd' THEN _end = _start + INTERVAL _interval
    WHEN _type = 'm' THEN _end = _start + INTERVAL MONTH(_interval)
    WHEN _type = 'y' THEN _end = _start + INTERVAL YEAR(_interval)
  END CASE

END $$

DELIMITER ;

and it give me this instead: Error 1064 You have an error in sql syntax....... What's wrong? How can i fix it? [bawling][dead] [help] Thanks again for helping

Sander

145

Sander

Oct 30, 2005 at 7:26 AM

A CASE is something you use inside a SELECT query (or other query). I don't think you can use it on it's own like this.

Michalson

177

Michalson

Oct 30, 2005 at 9:16 AM

Could you confirm what SQL provider you are actually using.

Wan

154

Wan

Nov 1, 2005 at 9:45 AM

In MS SQL it would look something like this:

  SELECT _end = CASE _type    WHEN 'd' THEN _start + INTERVAL _interval    WHEN 'm' THEN _start + INTERVAL MONTH(_interval)    WHEN 'y' THEN _start + INTERVAL YEAR(_interval)  END

Also, you would need to use a DATEADD function, but seeing the INTERVAL keyword I guess you're using MySQL.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.