Differences

This shows you the differences between two versions of the page.

--- dev:sql:functions:case [2010/07/04 02:09]
tdo
+++ dev:sql:functions:case [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
-====== Omnidex SQL Function: CASE ======
 {{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Function: CASE ======
 ===== Description =====
-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.
+The CASE function performs simple IF ... THEN ... ELSE ... END logic within a function.  Typically this is used to set a value based on a set of different conditions.  The conditions are evaluated in the order they appear in the function until a condition proves TRUE, at which point the THEN expression is executed. If none of the conditions prove TRUE, then the final ELSE expression is executed.
-CASE supports multiple conditions per case.
+This functions returns the same datatype as the THEN clauses in the function.  All of the THEN expressions in a CASE statement must be of the same datatype.
 ===== Syntax =====
+The CASE function supports two standard syntaxes:
   CASE
-      WHEN condition_expression THEN scalar_expression
+      WHEN conditional_expression_1 THEN scalar_expression_1
+      WHEN conditional_expression_2 THEN scalar_expression_2
       ELSE scalar_expression
   END
-or
+and
   CASE scalar_expression_1
       WHEN scalar_expression_2 THEN scalar_expression_3
-      ELSE scalar_expression_4
+      WHEN scalar_expression_4 THEN scalar_expression_5
+      ELSE scalar_expression_6
   END
+== conditional_expression ==
+Required. A conditional expression using SQL language that must prove TRUE in order for the function to return the corresponding scalar expression.  A conditional expression is generally equivalent to criteria used in a WHERE clause of a SQL SELECT statement, such as "state in ('CA','OR','WA')" or "birthdate >= 'Jan 1, 1980'.
+== scalar_expression ==
+Required.  An expression using SQL language that produces a value.  A scalar expression is generally equivalent to a select item in the SQL SELECT statement, and can be a character expression, numeric expression or date expression.
 ===== Example =====
-==== Example 1 Condition Expression ====
+==== Example 1: CASE against column values ====
-<code SQL>
-  CASE
-  WHEN PRODUCTS.CATEGORY =30 THEN 'Furniture'
-  WHEN PRODUCTS.CATEGORY = 70 THEN 'Machines'
-  WHEN PRODUCTS.CATEGORY = 90 THEN 'Paper Products'
-  ELSE 'Office Supplies'
-  END
-</code>
-==== Example 2 Scalar Expression ====
+<code>
-<code SQL>
+> select name,
-  CASE DATES.MO
+>>        gender,
-  WHEN 'Jan' THEN 1
+>>        case gender
-  WHEN 'Feb' THEN 2
+>>          when 'M' then 'Male'
-  WHEN 'Mar' THEN 3
+>>          when 'F' then 'Female'
-  WHEN 'Apr' THEN 4
+>>          else          'Did not specify'
-  ELSE 0
+>>          end gender_desc
-  END
+>>   from individuals;
+NAME                                                G  GENDER_DESC
+--------------------------------------------------  -  ---------------
+MS. MARY LEWIS                                      F  Female
+MRS. ALICE R TOLLIVER                               F  Female
+MR. SIDNEY K TOLLIVER                               M  Male
+MRS. JENNIFER J HOPKINS                             F  Female
+MR. CHARLIE HOPKINS                                 M  Male
+MS. KIMBERLY BLUE                                   F  Female
+KATHLEEN TUCKER                                     F  Female
 </code>
-==== Example 3 Select Statement with Conditional Expression ====
+==== Example 2: CASE using complex expressions ====
-<code SQL>
+<code>
-select company,
+> select name,
+>>       gender,
+>>       birthdate,
+>>       case
+>>         when gender = 'M' and $compare_dates(birthdate, current_date, 'YY') < 18
+>>           then 'Mstr.'
+>>         when gender = 'M' and $compare_dates(birthdate, current_date, 'YY') >= 18
+>>           then 'Mr.'
+>>         when gender = 'F' and $compare_dates(birthdate, current_date, 'YY') < 18
+>>           then 'Miss'
+>>         when gender = 'F' and $compare_dates(birthdate, current_date, 'YY') >= 18
+>>           then 'Ms.'
+>>         else
+>>                ''
+>>         end salutation
+>>  from individuals;
-case
+NAME                                                G  BIRTHDATE     SALUT
-when customers.state='CA' then 'CALIFORNIA'
+--------------------------------------------------  -  ------------  -----
-when customers.state='CO' then 'COLORADO'
+MS. MARY LEWIS                                      F    1938-07-14  Ms.
-when customers.state='NY' then 'NEW YORK'
+MRS. ALICE R TOLLIVER                               F    1957-11-22  Ms.
-when customers.state='NJ' then 'NEW JERSEY'
+MR. SIDNEY K TOLLIVER                               M    1960-07-28  Mr.
-else 'OTHER'
+MRS. JENNIFER J HOPKINS                             F    1929-08-22  Ms.
-end
+MR. CHARLIE HOPKINS                                 M    1933-07-09  Mr.
+MS. KIMBERLY BLUE                                   F    1944-08-01  Ms.
-from customers
+KATHLEEN TUCKER                                     F    2004-09-14  Miss
-where state in ('ca','co','ny','nj','az','fl','nv')
-order by state;
 </code>
-==== Example 4 Select Statement with Scalar Expression ====
+====== Additional Resources ======
-<code SQL>
-select company, contact,
-case customers.state
+See also:
-when 'AZ' then 'ARIZONA'
+{{page>dev:sql:functions:see_also_logic&noheader&nofooter&noeditbtn}}
-when 'FL' then 'FLORIDA'
-when 'NV' then 'NEVADA'
-else 'OTHER'
-end
-from customers
-where company='systems'
-order by state
-</code>
 {{page>:bottom_add&nofooter&noeditbtn}}
+=====

Back to top

dev/sql/functions/case.1278209349.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes