Omnidex SQL Function: CASE

The CASE function handles simple IF - THEN - ELSE logic at a select-item level. The WHEN conditions are evaluated in order until a condition proves TRUE, at which point the THEN expression is executed. All of the THEN expressions in a CASE statement must be of the same data type: numeric, character, or date.

CASE supports multiple conditions per case.

CASE
    WHEN condition_expression THEN scalar_expression
    ELSE scalar_expression
END

or

CASE scalar_expression_1
    WHEN scalar_expression_2 THEN scalar_expression_3
    ELSE scalar_expression_4
END

  CASE
  WHEN PRODUCTS.CATEGORY < 30 THEN 'Furniture' 
  WHEN PRODUCTS.CATEGORY < 70 THEN 'Machines'
  WHEN PRODUCTS.CATEGORY < 90 THEN 'Paper Products' 
  ELSE 'Office Supplies'
  END

  CASE DATES.MO 
  WHEN 'Jan' THEN 1 
  WHEN 'Feb' THEN 2 
  WHEN 'Mar' THEN 3 
  WHEN 'Apr' THEN 4 
  ELSE 0 
  END

SELECT company,
 
CASE 
WHEN customers.state='CA' THEN 'CALIFORNIA' 
WHEN customers.state='CO' THEN 'COLORADO' 
WHEN customers.state='NY' THEN 'NEW YORK' 
WHEN customers.state='NJ' THEN 'NEW JERSEY' 
ELSE 'OTHER' 
END
 
FROM customers 
WHERE state IN ('ca','co','ny','nj','az','fl','nv') 
ORDER BY state

SELECT company, contact,
 
CASE customers.state 
WHEN 'AZ' THEN 'ARIZONA' 
WHEN 'FL' THEN 'FLORIDA' 
WHEN 'NV' THEN 'NEVADA' 
ELSE 'OTHER' 
END
 
FROM customers 
WHERE company='systems' 
ORDER BY state