240 likes | 492 Views
SQL Transformation Oracle and ANSI Standard SQL. Lecture 10. SQL Transformation. Transformation Defined DECODE() Function CASE Statement Case Study. SQL Transformation Defined. SQL Transformation aggregates and shifts rows of data into columns of information. SQL Transformation uses:
E N D
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