mysqlmysql-workbenchsqlyog

mysql case when in clauses

I have to check the value of @inv_status with below MySQL code but not getting the desired output. I am expecting "success 1" but it is giving 0, please suggest.

SET @ip_AllocationId = 1;
SET @ip_status = NULL;

SET @inv_status = 4;


SELECT  CASE WHEN @inv_status IN(
SELECT
CASE 
  WHEN @ip_AllocationId = 1 AND ISNULL(@ip_status) THEN (SELECT CONCAT("1",",","4"))
  WHEN @ip_AllocationId = 1 AND @ip_status=1 THEN 1
  WHEN @ip_AllocationId = 1 AND @ip_status=2 THEN 4
  ELSE NULL
 END AS filter
) THEN "1" ELSE "0" END AS "success";
Solution

  • Your code is equivalent to this:

    SET @ip_AllocationId = 1;
SET @ip_status = NULL;
SET @inv_status = 4;

SELECT @inv_status = (
  CASE 
    WHEN @ip_AllocationId = 1 AND @ip_status IS NULL THEN CONCAT('1',',','4')
    WHEN @ip_AllocationId = 1 AND @ip_status=1 THEN 1
    WHEN @ip_AllocationId = 1 AND @ip_status=2 THEN 4
  END
) AS success;

    What you are doing is compare @inv_status to the string '1,4' because the boolean expression @ip_AllocationId = 1 AND @ip_status IS NULL of the 1st branch of the CASE expression is TRUE.
    So the result is FALSE and you get 0.
    I suspect that you want is something like this:

    SET @ip_AllocationId = 1;
SET @ip_status = NULL;
SET @inv_status = 4;

SELECT FIND_IN_SET(@inv_status,
  CASE 
    WHEN @ip_AllocationId = 1 AND @ip_status IS NULL THEN CONCAT('1',',','4')
    WHEN @ip_AllocationId = 1 AND @ip_status=1 THEN '1'
    WHEN @ip_AllocationId = 1 AND @ip_status=2 THEN '4'
  END
) > 0 AS success;

    This way you check the existence of @inv_status in a comma separated list of values instead of a straight comparison.
    See the demo.