Boolean value is not recognized when using CASE WHEN-CodePudding

I am using SQL in snowflake. I am trying to use CASE WHEN to develop indicators for smoking status. When I run the code, I get an error:

Boolean value '428061000124105' is not recognized.

The code I am using is:

SELECT 
  history.PATIENT_ID as pat,
  history.SMOKING_STATUS AS smoke,
 CASE
  WHEN smoke = '428061000124105' OR '428071000124103' THEN 'smoker'
  WHEN smoke = '8517006' THEN 'ex_smoker'
ELSE 'NA'
END AS smoking_status
FROM 'db'.'schema'.'table';

Is this a data issue? What is the best way to solve this?

CodePudding user response：

Since you are providing column name only in when condition, SQL server is interpreting it as a boolean value.

Please try changing it to WHEN smoke = '428061000124105' OR smoke = '428071000124103' THEN 'smoker'

CodePudding user response：

This might show why the error is happening:

SELECT column1 as smoke
    ,CASE 
        WHEN smoke = '1234' THEN 'booleans are equal to text represntations of numbers'
        ELSE 'NA'
    END as smoke_test
FROM VALUES (true), (false);

gives:

Boolean value '1234' is not recognized

This is say, "you have a boolean value" (aka only true or false) and you are comparing it to a string. Don't do that.

If you insist of comparing 'true' or 'false' to text numbers. You can cast the boolean to a TEXT

SELECT column1 as smoke
    ,CASE 
        WHEN smoke::text = '1234' THEN 'booleans are equal to text represntations of numbers'
        WHEN smoke::text = '1' THEN 'one'
        WHEN smoke::text = '0' THEN 'zero'
        ELSE 'NA'
    END as smoke_test
FROM VALUES (true), (false);

SMOKE	SMOKE_TEST
TRUE	NA
FALSE	NA

which is the same as

SELECT column1 as smoke
    ,'NA'as smoke_test
FROM VALUES (true), (false);

or you can cast bool to INT at which point the TEXT and NUMBER compare will do an auto cast to TEXT after turning BOOL so a null,0,1

SELECT column1 as smoke
    ,CASE 
        WHEN smoke::int = '1234' THEN 'booleans are equal to text represntations of numbers'
        WHEN smoke::int = '1' THEN 'one'
        WHEN smoke::int = '0' THEN 'zero'
        ELSE 'NA'
    END as smoke_test
FROM VALUES (true), (false), (null);