Nesting Functions

Nesting Functions

• Single-row functions can be nested to any level.
• Nested functions are evaluated from deepest level to the least deep level.

General Functions

These functions work with any data type and pertain to using null value.
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, ..., exprn).

NVL Function

• Converts a null to an actual value.
• Data types that can be used are date, character, and number.
• Data types must match:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')

Using the NVL Function

Using the NVL2 Function

Using the NULLIF Function

Using the COALESCE Function

• The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
• If the first expression is not null, it returns that expression; otherwise, it does a COALESCE of the remaining expressions.

Using the COALESCE Function

Conditional Expressions

• Give you the use of IF-THEN-ELSE logic within a SQL statement
• Use two methods:
– CASE expression
– DECODE function

The CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:

Using the CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:

Using the DECODE Function.

Display the applicable tax rate for each employee in department 80.

Other useful Links :

• http://www.developerchoice.com/

• http://www.sqlserverquestions.blogspot.com