Specefic if else statement in select query

Question

I tried this select query with an IF statement but it didn't work,

Have you any solution

thank you

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcedure`()
BEGIN
   set @sql=' Select IF(field1 = 1, \'Oui\', IF(field2 != 0 and field1 = 0,\'En 

    cours\', IF(field2 = 0 and field1 = 0,\'non\', \'non\'),\'non\'))  as Payed
    from myTable';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

   END$$
DELIMITER ;

error message :

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 '),'non')) as Payed

You Can Use Case Statement instead of IF.

Osman
– Osman

2016-08-23 10:25:19 +00:00
Commented Aug 23, 2016 at 10:25 — Osman
– Osman, Commented Aug 23, 2016 at 10:25

Tim Biegeleisen · Accepted Answer · 2016-08-23 10:44:28Z

1

This answer may not fix your direct problem, but if you use CASE statements, the logic gets a bit easier to read:

SELECT CASE WHEN field1 = 1 THEN 'Oui'
            WHEN field2 != 0 AND field1 = 0 THEN 'Encours'
            ELSE 'non'
       END AS Payed
FROM myTable

The ANSI compliant way to escape a single quote is by using two of them, e.g. ''. So your @sql variable would then look something like this:

SET @sql= 'SELECT CASE WHEN field1 = 1 THEN ''Oui''
        WHEN field2 != 0 AND field1 = 0 THEN ''Encours''
            ELSE ''non''
       END AS Payed
FROM myTable';

edited Aug 23, 2016 at 10:44

answered Aug 23, 2016 at 10:27

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

@Shadow Part of the OP issue is using IF, which requires many layers of nested parentheses. I don't usually undelete a downvoted answer, but I did in this case because I think using CASE would solve two problems at once.

Shadow Over a year ago

My downvote was due to the explanation that if() function cannot be used the way it was used in the OP. Since you corrected the answer, I withdraw the downvote.

Collectives™ on Stack Overflow

Specefic if else statement in select query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related