Differences

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

Link to this comparison view

Next revision
Previous revision
dev:sql:functions:case [2009/12/04 22:20]
tdo created
dev:sql:functions:case [2016/06/28 22:38] (current)
Line 1: Line 1:
 {{page>:​top_add&​nofooter&​noeditbtn}} {{page>:​top_add&​nofooter&​noeditbtn}}
-<html><div align="​center"><​span style="​color:red">​DRAFT</​span></​div></​html>​+{{page>:sql_bar&​nofooter&​noeditbtn}}
 ====== Omnidex SQL Function: CASE ====== ====== Omnidex SQL Function: CASE ======
 ===== Description ===== ===== Description =====
-The CASE function ​handles ​simple IF THEN ELSE logic at 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 ​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 ===== ===== Syntax =====
 +
 +The CASE function supports two standard syntaxes:
 +
   CASE   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       ELSE scalar_expression
   END   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 =====
-==== Example 1 ==== +==== Example 1: CASE against column values ​====
-==== Example 2 ====+
  
 +<​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>:​bottom_add&​nofooter&​noeditbtn}}
 +
 +=====
 +
 
Back to top
dev/sql/functions/case.1259965209.txt.gz · Last modified: 2016/06/28 22:38 (external edit)