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
• If the first expression is not null, it returns that expression; otherwise, it does a COALESCE of the remaining expressions.
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
• Use two methods:
– CASE expression
– DECODE function
Using the CASE Expression
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
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.
Display the applicable tax rate for each employee in department 80.
database concepts
