Writing SQL code with greater than and less than functions

2278 views mysql

WhenI try to run this code:

CASE WHEN a.promotionflag=0 THEN 'NoPromotion' 
WHEN (Sales)/ISNULL(Margin,0) > -3.0000 
AND (Sales)/ISNULL(Funding, 0) < 0 THEN 'RED'
WHEN (Sales) <0 THEN 'RED'
WHEN (Sales)= 0 and (Margin) <0 then 'RED'
WHEN (Sales)/ISNULL(Margin, 0) >= 0 THEN 'GREEN'
WHEN (Sales) IS NULL THEN 'NonPriority'
WHEN (Margin) IS NULL THEN 'NonPriority'
END AS Promotions`

I receive an error that says "incorrect syntax near >" and brings me to the first instance where there is a >. Then when I hover about that, the first time I received the error it also mentioned a "boolean function."

I want this code to return me the color values based on the above criteria.

answered question

"WHEN (Sales)= 0 and (Margin) <0 then 'RED'" There is your problem. Also, MySql does not use T-Sql - only Sybase and SQL Server use it.

1 Answer


ISNULL takes only one argument and returns 1 or 0 in MySQL. You are probably looking for IFNULL. Also, using 0 as NULL replacement is a bad idea, as you'll get division by 0.

posted this

Have an answer?


Please login first before posting an answer.