SQL Transformation Oracle and ANSI Standard SQL

SQL TransformationOracle and ANSI Standard SQL Lecture 10

SQL Transformation • Transformation Defined • DECODE() Function • CASE Statement • Case Study

SQL TransformationDefined • SQL Transformation aggregates and shifts rows of data into columns of information. • SQL Transformation uses: • An aggregation function with a nested DECODE() function to conditionally evaluate, then accept or discard results. • An aggregation function with a nested CASE statement to conditionally evaluate, then accept or discard results. • Typically the results from the aggregation function are assigned a new column name that describes what type of value is returned.

SQL TransformationDECODE() function • The DECODE() function is specific to the Oracle database implementation. • The DECODE() function has three prototypes: • One acts as a simple if-then statement and discards null results. • One acts as a simple if-then-elsestatement and uses the met (if condition is true) or the unmet (else the condition is false) values equally. • One acts as a case statement or if-then, else-if, and else statement; however, the else can be omitted. • The DECODE() function supports nesting of other functions, including DECODE() function calls.

SQL TransformationDECODE() function: if-then SELECT DECODE(evaluation_expression ,comparison_expression ,resulting_expression) FROM dual; • The evaluation expression can be a column variable, function expression, or literal (typically a DATE, NUMBER or VARCHAR2 data type. • The comparison expression can be a column variable, function expression, or literal (typically a DATE, NUMBER or VARCHAR2 data type. • The resulting expression can be a column variable, function expression, or literal (typically a DATE, NUMBER or VARCHAR2 data type.

SQL TransformationDECODE() function: if-then SELECT DECODE('One' ,'One' ,'It''s true!') result FROM dual; RESULT ---------- It's true! 1 row selected.

SQL TransformationDECODE() function: if-then SELECT DECODE('One' ,'Two' ,'It''s true!') result FROM dual; RESULT ---------- <Null> 1 row selected.

SQL TransformationDECODE() function: if-then-else SELECT DECODE('One' ,'Two' ,'It''s true!' ,'It''s false!') result FROM dual; RESULT ----------- It's false! 1 row selected.

SQL TransformationDECODE() function: case SELECT DECODE('One' ,'Two','It''s two!' ,'One','It''s one!') result FROM dual; RESULT ----------- It's one! 1 row selected.

SQL TransformationDECODE() function: case SELECT DECODE('One' ,'Two','It''s two!' ,'Three','It'' three!') result FROM dual; RESULT ------------- <Null> 1 row selected.

SQL TransformationDECODE() function: case SELECT DECODE('One' ,'Two','It''s two!' ,'Three','It'' three!' ,'It''s not one!') result FROM dual; RESULT ------------- It's not one! 1 row selected.

SQL TransformationCASE statement • The CASE statement is ANSI defined and not specific to an Oracle database implementation. • The CASE function has one prototype: • It always uses the WHEN clause to evaluate a comparison expression as TRUE or FALSE; and TRUE meets the condition while FALSE fails. • It can nest CASE statements in the THEN clause. • It returns a NULL if none of the WHEN clauses return TRUE and there is no ELSE clause, which can be omitted. • The CASE statement supports nesting of other functions, in both the WHEN, THEN and ELSE clauses.

SQL TransformationCASE statement: Prototype SELECT CASE WHEN comparative_expression THEN resulting_expression END result FROM dual; • The comparative expression can be a comparison of two column variables, function expressions, or literals (typically a DATE, NUMBER or VARCHAR2 data types. • The resulting expression can be a column variable, function expression, or literal (typically a DATE, NUMBER or VARCHAR2 data type.

SQL TransformationCASE statement: true comparison SELECT CASE WHEN 'One' = 'One' THEN 'It''s one!' END result FROM dual; RESULT ----------- It's one! 1 row selected.

SQL TransformationCASE statement: false w/o an else SELECT CASE WHEN 'One' = 'Two' THEN 'It''s two!' END result FROM dual; RESULT ---------- <Null> 1 row selected.

SQL TransformationCASE statement: false w/an else SELECT CASE WHEN 'One' = 'Two' THEN 'It''s two!' ELSE 'It''s not one!' END result FROM dual; RESULT ---------- 'It's not one!' 1 row selected.

SQL TransformationCase Study: Problem Definition Name Null? Type ------------------ ----- ------------ TRANSACTION_ID NUMBER TRANSACTION_GROUP VARCHAR2(20) TRANSACTION_AMOUNT NUMBER TRANSACTION_DATE DATE • The TRANSACTION_ID is the primary key. • The TRANSACTION_ACCOUNT and TRANSACTION_DATE values are basic dimensions or filters. • The TRANSACTION_AMOUNT values are basic facts or data. • Problem: The amount needs to be aggregated by month and group by account with months as the columns and accounts as the rows; and a both a sum total for accounts and months.

SQL TransformationCase Study: Problem Resolution • The combination of a SUM() function and a CASE statement enables: • Adding columns in rows where another column only meets a condition. • Creating result set columns by using column aliasing. • The conditions in nested CASE statements within SUM() functions can differ between result set columns without impacting the GROUP BY process. • The subtotaling of columns can be done by: • Using the Oracle implementation specific GROUP BY CUBE() to derive the column total and the ORDER BY to sort the total to the bottom. • Using a generic ANSI approach with set operations derives the column total at various levels, and then uses the ORDER BY to sort them appropriately.

SQL TransformationCase Study: Step #1: Pivoting SELECT transaction_account account , SUM(CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount END) AS jan , SUM(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2006 THEN transaction_amount END) AS ytd FROM transactions GROUP BY transaction_account; • A sum of data, or a fact, is now identified by both the month, and year-to-date columns and account number rows.

SQL TransformationCase Study: Step #2: Cubing result SELECT CASE WHEN GROUPING(transaction_account) = 1 THEN 'Total:' ELSE transaction_account END AS account , SUM(CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount END) AS jan , SUM(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2006 THEN transaction_amount END) AS ytd FROM transactions GROUP BY CUBE (transaction_account) ORDER BY transaction_account;

SQL TransformationCase Study: ANSI Step #1: Detail SELECT t.transaction_account account , SUM(CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN t.transaction_amount END) AS jan , SUM(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2006 THEN transaction_amount END) AS ytd FROM transactions t GROUP BY t.transaction_account UNION ALL … total_summary … ORDER BY 1;

SQL TransformationCase Study: ANSI Step #2: Summary … detail_transaction_account_rows … UNION ALL SELECT 'Totals:' account , SUM(CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN t.transaction_amount END) AS jan , SUM(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2006 THEN transaction_amount END) AS ytd FROM transactions t ORDER BY 1;

SQL TransformationCase Study: Subtotals • Subtotals in these types of problems makes solving them more complex. • Subtotals can be developed as independent queries joined by set operations, provided: • There is a means to order detail rows. • There is a means to insert subtotal rows below their respective detail rows. • There is a business model that clearly establishes the relationship between detail and subtotals.

Summary • Transformation Defined • DECODE() Function • CASE Statement • Case Study

SQL Transformation Oracle and ANSI Standard SQL