Differences

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

--- dev:sql:functions:case [2009/12/04 22:20]
tdo created
+++ dev:sql:functions:case [2010/07/27 20:05]
els
@@ Line 1: / Line 1: @@
+{{page>:top_add&nofooter&noeditbtn}}
+{{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Function: CASE ======
+===== Description =====
+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.
+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 conditional_expression_1 THEN scalar_expression_1
+      WHEN conditional_expression_2 THEN scalar_expression_2
+      ELSE scalar_expression
+  END
+and
+  CASE scalar_expression_1
+      WHEN scalar_expression_2 THEN scalar_expression_3
+      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: CASE against column values ====
+<code>
+> select name,
+>>        gender,
+>>        case gender
+>>          when 'M' then 'Male'
+>>          when 'F' then 'Female'
+>>          else          'Did not specify'
+>>          end gender_desc
+>>   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 2: CASE using complex expressions ====
+<code>
+> 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;
+NAME                                                G  BIRTHDATE     SALUT
+--------------------------------------------------  -  ------------  -----
+MS. MARY LEWIS                                      F    1938-07-14  Ms.
+MRS. ALICE R TOLLIVER                               F    1957-11-22  Ms.
+MR. SIDNEY K TOLLIVER                               M    1960-07-28  Mr.
+MRS. JENNIFER J HOPKINS                             F    1929-08-22  Ms.
+MR. CHARLIE HOPKINS                                 M    1933-07-09  Mr.
+MS. KIMBERLY BLUE                                   F    1944-08-01  Ms.
+KATHLEEN TUCKER                                     F    2004-09-14  Miss
+</code>
+====== Additional Resources ======
+See also:
+{{page>dev:sql:functions:see_also_logic&noheader&nofooter&noeditbtn}}
+{{page>:bottom_add&nofooter&noeditbtn}}
+=====
 {{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}}
 ===== 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.
+CASE supports multiple conditions per case.
 ===== Syntax =====
@@ Line 11: / Line 116: @@
   END
+or
+  CASE scalar_expression_1
+      WHEN scalar_expression_2 THEN scalar_expression_3
+      ELSE scalar_expression_4
+  END
 ===== Example =====
-==== Example 1 ====
+==== Example 1 Condition Expression ====
-==== Example 2 ====
+<code>
+SELECT ...
+  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 SQL>
+  CASE DATES.MO
+  WHEN 'Jan' THEN 1
+  WHEN 'Feb' THEN 2
+  WHEN 'Mar' THEN 3
+  WHEN 'Apr' THEN 4
+  ELSE 0
+  END
+</code>
+==== Example 3 Select Statement with Conditional Expression ====
+<code SQL>
+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;
+</code>
+==== Example 4 Select Statement with Scalar Expression ====
+<code SQL>
+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
+</code>
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

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

Sitemap Recent Changes