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