Friday, January 21, 2011

Apply conditional expressions in a SELECT statement

NVL Function - If we want to test two or more  fields and get the value of the first non-null field, we have to use NVL function.  It has following syntax:
NVL( a, b) . It can be translated like that: If 'a' is not null will be returned 'a', else will be returned 'b':

Examples:
SQL> select nvl(commission_pct, 0)from employees;
NVL(COMMISSION_PCT,0)
---------------------
                    0
                    0
                    0
                   .4

                   .3
                   .3
 ...





NVL2 Function - it extends the functionallity from NVL function. Its syntax is NVL2(a,b,c) ---> if 'a' is not null then return 'b' else return 'c'.

SQL> select last_name, nvl2(commission_pct, 'has commission', 'has
n') from employees;
LAST_NAME                 NVL2(COMMISSION_PC
------------------------- ------------------
Matos                     has not commission
Vargas                    has not commission
Russell                   has commission
Partners                  has commission
Errazuriz                 has commission
Cambrault                 has commission
Zlotkey                   has commission
Sullivan                  has not commission
Geoni                     has not commission
Sarchand                  has not commission
Bull                      has not commission
Dellinger                 has not commission
Cabrio                    has not commission
.....

NULLIF - if we need to test two items for equility, we have to use NULLIF function. It has following syntax:
NULLIF(expression1, expression2).
It will return expression1 if two expressions are not equal. If they are equal, NULLIF will return NULL value.
Examples:

SQL> select nullif (mruhtel, mruhtel) from dual;
select nullif (mruhtel, mruhtel) from dual
                        *
ERROR at line 1:
ORA-00904: "MRUHTEL": invalid identifier

SQL> select nullif ('mruhtel', 'mruhtel') from dual;
NULLIF(
-------

SQL> select nullif ('mruhtel', 'mruhtels') from dual;
NULLIF(
-------
mruhtel
SQL> select nullif ('mruhtel', 'Mruhtel') from dual;   ----> it is case sensitive
NULLIF(
-------
mruhtel

COALESCE Function - It evaluates a list of expressions against NULL and it returns first non-null expression from the list. For example:
SQL> select product_id, list_price, min_price,
  2  coalesce(list_price, min_price, 0) "Price"
  3  from product_information
  4  where rownum <11;

PRODUCT_ID LIST_PRICE  MIN_PRICE      Price
---------- ---------- ---------- ----------
      1797        349        288        349
      2459        699        568        699
      3127        498        444        498
      2254        453        371        453
      3353        489        413        489
      3069        436        350        436
      2253        399        322        399
      3354        543        478        543
      3072        567        507        567
      3334        612        512        612
10 rows selected.
SQL> select product_id, list_price, min_price,
  2  coalesce(list_price, min_price, 'no_price') "Price"
  3  from product_information;
coalesce(list_price, min_price, 'no_price') "Price"
                                *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
To avoid that error, all not null parameters MUST have data type comptatible with the first not null parameter. In that case must be NUMBER.

If we compare NVL function and COALESCE function, we will see that they can be used equally. For example:
coalesce(a, b) = nvl(a, b)
SQL> select product_id, list_price, min_price,
  2  coalesce(list_price, min_price) "Price"
  3  from product_information
  4  where rownum <11;
PRODUCT_ID LIST_PRICE  MIN_PRICE      Price
---------- ---------- ---------- ----------
      1797        349        288        349
      2459        699        568        699
      3127        498        444        498
      2254        453        371        453
      3353        489        413        489
      3069        436        350        436
      2253        399        322        399
      3354        543        478        543
      3072        567        507        567
      3334        612        512        612
10 rows selected.
SQL> select product_id, list_price, min_price,
  2  nvl(list_price, min_price) "Price"
  3  from product_information
  4  where rownum <11;
PRODUCT_ID LIST_PRICE  MIN_PRICE      Price
---------- ---------- ---------- ----------
      1797        349        288        349
      2459        699        568        699
      3127        498        444        498
      2254        453        371        453
      3353        489        413        489
      3069        436        350        436
      2253        399        322        399
      3354        543        478        543
      3072        567        507        567
      3334        612        512        612
10 rows selected.
CASE Function - it let us to evaluate first two items for equility and returns the third one if they are equal and onother one if they are not equal. It implaments the IF-THEN-ELSE logic without involking procedures.
It has two formats: simple case expression and searched case expression.

Simple case expression -
case expression
 when comparrison_expression
 then result
else return_expression
end

SIMPLE CASE EXPRESSION IS ENCLOSED WITHIN CASE.... END BLOCK!!!
If expression =  comparisson_expression, it will return result
If expression != comparisson_expression, it will return return_expression

Example:
SQL> select last_name, salary,
  2  case salary when 2100 then 'low'
  3  when 21000 then 'high'
  4  else 'medium' end
  5  from employees;

LAST_NAME                     SALARY CASESA
------------------------- ---------- ------
Bissot                          3300 medium
Atkinson                        2800 medium
Marlow                          2500 medium
Olson                           2100 low
Mallin                          3300 medium
Dellinger                       3400 medium...

Search case expression
case
when condition then result
[else result_expression]
end

SEARCH CASE EXPRESSION IS ENCLOSED ALSO WITHIN CASE.... END BLOCK!!!
If condition is evaluated to true, it will return result
If expression is evaluated to fals, it will return null value unless an ELSE is defined
Examples:
SQL> select salary,
  2  case when salary < 5000 then 'low'
  3  else 'high' end
  4  from employees;

    SALARY CASE
---------- ----
      2600 low
      2600 low
      4400 low
     13000 high
      6000 high
      6500 high
     10000 high

No comments:

Post a Comment