SQL CASE表达式报错（非布尔类型）：新手使用CASE语句遇错求助

Hey there! Let's tackle this CASE statement issue you're running into. That error message—Case Expression error (NON-Boolean) and An expression of non-boolean type specified in a context where a condition is expected, near 'and'—is pointing to a common mistake with how CASE statements are structured, especially when mixing simple value checks with multi-condition logic.

Why This Happens #

The error pops up when SQL expects a boolean condition (something that evaluates to true or false) but gets a non-boolean value instead, specifically right around your AND keyword. The most common culprit is mixing the two types of CASE syntax incorrectly:

• Simple CASE Syntax: Used for checking a single expression against fixed values (no AND/OR allowed here)
• Search CASE Syntax: Used for multi-condition logic (this is what you need when using AND/OR)

Example of the Wrong vs. Right Approach #

Let's say you tried writing something like this (which triggers the error):

-- ❌ Wrong: Mixing simple CASE with AND logic
SELECT
  CASE customer_status
    WHEN 'active' AND total_purchases > 10 THEN 'VIP'
    ELSE 'Regular'
  END AS customer_tier
FROM customers;

Here, SQL interprets 'active' AND total_purchases > 10 as a single value to compare against customer_status—but 'active' is a string, not a boolean, so the AND operation breaks everything.

The correct version uses the search CASE syntax:

-- ✅ Correct: Using search CASE for multi-condition logic
SELECT
  CASE
    WHEN customer_status = 'active' AND total_purchases > 10 THEN 'VIP'
    WHEN customer_status = 'active' AND total_purchases <= 10 THEN 'Loyal'
    ELSE 'Regular'
  END AS customer_tier
FROM customers;

Steps to Fix Your Query #

• Go to the line near the AND keyword mentioned in the error.
• Check if you're using the simple CASE [column] WHEN ... syntax with AND/OR. If yes, switch to the search CASE WHEN [condition] THEN ... structure.
• Ensure every WHEN clause contains a valid boolean condition (e.g., column = value, column > number, column IS NOT NULL).

If you can share a snippet of your actual query, I can help you spot the exact spot to fix!

内容的提问来源于stack exchange，提问作者Skorpion