1Z0-149: Using Conversion Functions and Conditional Expressions

What this domain tests

This domain covers explicit and implicit data conversion, format models, and conditional expressions used to transform output.

Core rules

• TO_CHAR(number, 'fmt') and TO_CHAR(date, 'fmt') format values. Common date elements are YYYY, MM, DD, HH24, MI, SS, DAY, MON; FM removes padding.
• TO_DATE(string, 'fmt') interprets a string. TO_NUMBER(string, 'fmt') converts numeric text.
• YY uses the current century. RR uses a sliding century: 00-49 maps to the current century, 50-99 to the previous century (relative to the current year).
• Implicit conversion happens when types are mixed, but it depends on NLS settings and can prevent index use.
• CASE supports simple and searched forms and can return different expressions per condition.
• DECODE(expr, search1, result1, ..., default) performs equality tests and treats two NULLs as equal.
• NVL, NVL2, NULLIF, and COALESCE handle NULL substitution and comparison.

Worked example

SELECT TO_CHAR(hire_date, 'DD-MON-YYYY') AS hired, CASE WHEN salary >= 10000 THEN 'High' WHEN salary >= 5000 THEN 'Medium' ELSE 'Low' END AS band FROM employees;

The format model renders each date component. The searched CASE evaluates conditions in order; the first true condition wins, so a salary of 12000 is High and never reaches Medium. Without ELSE, unmatched rows return NULL.

Traps

• Using YY for birth dates near a century boundary; RR is often intended.
• Writing CASE salary WHEN NULL THEN ...; equality with NULL is unknown. Use WHEN salary IS NULL.
• Forgetting that DECODE considers NULL equal to NULL, unlike CASE.
• Omitting the format model and relying on NLS date formats.
• Assuming TO_CHAR changes the stored data type; it only changes the displayed string.