OCP-047 NVL NULLIF NVL2 COALESCE

274. Given below is a list of functions and their purpose in random order.
Function Purpose
1)NVL a) Used for evaluating NOT NULL and NULL values
2)NULLIF b) Used to return the first nonnull alues in a list of expressions
3)COALESCE c) Used to compare two expressions. If both are same, it returns
NULL;otherwise, it returns only the first expression.
4)NVL2 d) Used to convert NULL values to actual values
Identify the correct combination of functions and their usage.
A. 1a,2c,3b,4d
B. 1d,2c,3b,4a
C. 1b,2c,3d,4a
D. 1d,2b,3c,4a

Answer: B

NVL

Syntax

Purpose

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returnsexpr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error. The implicit conversion is implemented as follows:

• If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

• If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

  See Also:

  Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and  "Numeric Precedence" for information on numeric precedence

Examples

The following example returns a list of employee names and commissions, substituting "Not Applicable" if the employee receives no commission:

SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
   "COMMISSION" FROM employees
   WHERE last_name LIKE 'B%'
   ORDER BY last_name;
 
LAST_NAME                 COMMISSION
------------------------- ----------------------------------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .1
Bates                     .15
Bell                      Not Applicable

NULLIF

Syntax

Description of the illustration nullif.gif

Purpose

NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.

If both arguments are numeric datatypes, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype. If the arguments are not numeric, then they must be of the same datatype, or Oracle returns an error.

The NULLIF function is logically equivalent to the following CASE expression:

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

See Also:

"CASE Expressions"

Examples

The following example selects those employees from the sample schema hr who have changed jobs since they were hired, as indicated by a job_id in thejob_history table different from the current job_id in the employees table:

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
   FROM employees e, job_history j
   WHERE e.employee_id = j.employee_id
   ORDER BY last_name;

LAST_NAME                 Old Job ID
------------------------- ----------
De Haan                   AD_VP
Hartstein                 MK_MAN
Kaufling                  ST_MAN
Kochhar                   AD_VP
Kochhar                   AD_VP
Raphaely                  PU_MAN
Taylor                    SA_REP
Taylor
Whalen                    AD_ASST

COALESCE

Syntax

Description of the illustration coalesce.gif

Purpose

COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.

Oracle Database uses short-circuit evaluation. That is, the database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

If all occurrences of expr are numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and  "Numeric Precedence" for information on numeric precedence

This function is a generalization of the NVL function.

You can also use COALESCE as a variety of the CASE expression. For example,

COALESCE (expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Similarly,

COALESCE (expr1, expr2, ..., exprn), for n>=3

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END

See Also:

NVL and  "CASE Expressions"

Examples

The following example uses the sample oe.product_information table to organize a clearance sale of products. It gives a 10% discount to all products with a list price. If there is no list price, then the sale price is the minimum price. If there is no minimum price, then the sale price is "5":

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050;

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      2382        850        731        765
      3355                                5
      1770                    73         73
      2378        305        247      274.5
      1769         48                  43.2

NVL2

Syntax

Description of the illustration nvl2.gif

Purpose

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. Ifexpr1 is null, then NVL2 returns expr3.

The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.

If the datatypes of expr2 and expr3 are different:

• If expr2 is character data, then Oracle Database converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. Oracle returns VARCHAR2 in the character set of expr2.

• If expr2 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

  See Also:

  Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and  "Numeric Precedence" for information on numeric precedence

Examples

The following example shows whether the income of some employees is made up of salary plus commission, or just salary, depending on whether thecommission_pct column of employees is null or not.

SELECT last_name, salary, NVL2(commission_pct, 
   salary + (salary * commission_pct), salary) income
   FROM employees WHERE last_name like 'B%'
   ORDER BY last_name;

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6882
Bates                           7300       8468
Bell                            4000       4000
Bernstein                       9500      11970
Bissot                          3300       3300
Bloom                          10000      12100
Bull                            4100       4100