Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
dev:sql:functions:case [2009/12/18 16:25]
tdo
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>​ 
-====== Omnidex SQL Function: CASE ====== 
 {{page>:​sql_bar&​nofooter&​noeditbtn}} {{page>:​sql_bar&​nofooter&​noeditbtn}}
 +====== 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: numericcharacter, 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 TRUEthen 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 ===== ===== 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
  
-or+and
  
   CASE scalar_expression_1   CASE scalar_expression_1
       WHEN scalar_expression_2 THEN scalar_expression_3       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   END
  
-===== Example ===== 
-==== Example 1 Condition Expression ==== 
-<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 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> +== conditional_expression ==
-select company,+
  
-case  +RequiredA 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'​. ​ 
-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 ​ +== scalar_expression == 
-where state in ('​ca'​,'​co'​,'ny','nj','az','fl','nv' + 
-order by state+Required. ​ An expression using SQL language that produces a value. ​ A scalar expression is generally equivalent to a select item in the SQL SELECT statementand can be a character expressionnumeric 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>​ </​code>​
-==== Example ​4 Select Statement with Scalar Expression ​==== +==== Example ​2: CASE using complex expressions ​====
-<code SQL> +
-select company, contact,+
  
-case customers.state  +<​code>​ 
-when 'AZ' then 'ARIZONA'  +> select name,  
-when 'FL' then 'FLORIDA'  +>> ​      ​gender,​ 
-when 'NV' then 'NEVADA'  +>> ​      ​birthdate,​ 
-else 'OTHER'  +>> ​      case  
-end+>> ​        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;​
  
-from customers ​ +NAME                                                G  BIRTHDATE ​    SALUT 
-where company='​systems' ​ +-------------------------------------------------- ​ -  ------------ ​ ----- 
-order by state+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>​ </​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.1261153554.txt.gz · Last modified: 2016/06/28 22:38 (external edit)