CODEWITHSUNDEEP
mysqlsql
Nia
Nia

Reputation: 151

Why is "INTERVAL" not a valid syntax in IF statement

I have a IF THEN ELSE statement in one of my stored prodecures.

 if (DAYOFWEEK((SELECT  DATE_ADD(_todaydate, INTERVAL (_sign * offset.value) DAY)))=7) 
 then
        SELECT  DATE_ADD(daytemp, INTERVAL (_sign * offset.value + 2) DAY) into `day`;
 elseif (DAYOFWEEK((SELECT  DATE_ADD(_todaydate, INTERVAL _sign * offset.value DAY)))=1) 
 then
        SELECT  DATE_ADD(daytemp, INTERVAL (_sign * offset.value + 1) DAY) into `day`;
 else
        Select (SELECT  DATE_ADD(_todaydate, INTERVAL _sign * offset.value DAY)) into `day`;
 END if;

where _todaydate is today's date,_sign is either +1 or -1 depending on if you want to find days in future or past and the offset is a number int showing how many days from _todaydate.

The query is meant to return me working day either in past or future depending upon the _sign * offset.value, but the problem is that sql returns me an error saying that "unexpected INTERVAL (interval) in the if statement"

I am not sure why I am getting this problem because the query it self looks fine to me. Can some one please see what I am missing...

Additional Information:

I have this IF statement in a "Select From" statement, Could that be the case of that this error is happening?

Upvotes: 0

Views: 107

Answers (2)

spencer7593
spencer7593

Reputation: 108410

Wouldn't the code be much easier to debug and understand, if we avoided repeating long expressions, wrote something like this (to accomplish what it looks like we are trying to achieve):

  DECLARE _dow INT;
  DECLARE _inc INT;

  SELECT DAYOFWEEK(DATE_ADD(_todaydate, INTERVAL (_sign * offset.value) DAY)) INTO _dow ;

  CASE _dow
    WHEN 7 THEN SET _inc = 2;
    WHEN 1 THEN SET _inc = 1;
    ELSE        SET _inc = 0;
  END CASE;  

  SELECT DATE_ADD(daytemp, INTERVAL (_sign * offset.value + _inc) DAY) INTO `day`;

To answer the question you asked, "why is INTERVAL not a valid syntax in IF statement"...

I'm not aware of any restriction in MySQL stored programs that prohibits the use of the keyword INTERVAL within an IF statement. Obviously, the INTERVAL keyword isn't part of the IF construct; the keyword is only valid in the context of some expressions.

FOLLOWUP

I notice that the rewrite above is not equivalent to the original. In the original, under the ELSE condition, it's DATE_ADD(_todaydate,. That differs from the other two conditions, where it's DATE_ADD(daytemp

A slight re-write to accommodate that difference, adding another variable _bdt, allows us to still just use two SELECT statements...

  DECLARE _bdt DATE;
  DECLARE _dow INT;
  DECLARE _inc INT;

  SELECT DAYOFWEEK(DATE_ADD(_todaydate, INTERVAL (_sign * offset.value) DAY)) INTO _dow ;

  CASE _dow
    WHEN 7 THEN 
      SET _inc = 2;
      SET _bdt = daytemp; 
    WHEN 1 THEN
      SET _inc = 1;
      SET _bdt = daytemp;
    ELSE
      SET _inc = 0;
      SET _bdt = _todaydate;
  END CASE;  

  SELECT DATE_ADD(_bdt, INTERVAL (_sign * offset.value + _inc) DAY) INTO `day`;

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Shouldn't your IF statement be like below

if (SELECT DAYOFWEEK(DATE_ADD(_todaydate, INTERVAL (_sign * offset.value) DAY))=7)

Moreover, I see that you are repeating the same condition multiple times, eventually executing the same query multiple times. Instead have this query executed before and store the value to a local variable and use that local variable in your conditional statement rather; which would be much efficient.

Upvotes: 3

Related Questions