Chapter 26
Interrogating the Data
“We live in a world of things and our only connection with them is that we know how to manipulate or to consume them.”
Table of Contents Chapter 26 – Interrogating the Data
– Quiz – What would the Answer be?
– Answer to Quiz – What would the Answer be?
– Quiz – Fill in the Blank Values in the Answer Set
– Answer to Quiz – Fill in the Blank Values in the Answer Set
– Quiz – Fill in the Answers for the NULLIF Command
– Quiz – Fill in the Answers for the NULLIF Command
– Answer to the ZEROIFNULL Question
– The COALESCE Answer Set The Coalesce Quiz
– Answers to the Coalesce Quiz
– The Basics of CAST (Convert And STore)
– Some Great CAST (Convert And STore) Examples
– Some Great CAST (Convert And STore) Examples
– Some Great CAST (Convert And STore) Examples
– A Teradata Extension – The Implied Cast
– The Basics of the CASE Statements
– The Basics of the CASE Statement shown Visually
– Valued Case Statement Vs Searched Case Statement
– When NO ELSE is present in CASE Statement
– When NO ELSE is present in CASE Statement
– When an ELSE is present in CASE Statement
– When NO ELSE is present in CASE Statement
– When an Alias is NOT used in a CASE Statement
– When an Alias is NOT used in a CASE Statement
– When NO ELSE is present in CASE Statement
– Combining Searched Case and Valued Case
– A Trick for getting a Horizontal Case
Quiz – What would the Answer be?
Sample_Table
| Class_Code | Grade_Pt | |
| Fr | 0 |
Can you guess what would return in the Answer Set?
Use the fake table above called Sample_Table and try and predict what the Answer will be if this query was running on the system.
Answer to Quiz – What would the Answer be?
Sample_Table
| Class_Code | Grade_Pt | |
| Fr | 0 |
Can you guess what would return in the Answer Set?
ERROR – Division by Zero
You get an error when you DIVIDE by ZERO! Let's turn the page and fix it!
The NULLIFZERO Command
What the NULLIFZERO does is make a zero into a NULL. So, the answer set you'd get from this is a simple ‘ FR’, and then a NULL value represented usually by a ‘? ’. If you have a calculation where a ZERO could kill the operation and you don't want that you can use the NULLIFZERO command to convert any zero value to a NULL value.
Quiz – Fill in the Blank Values in the Answer Set
Sample_Table
| Cust_No | Acc_Balance | Location | ||
| 0 | ? | 3 |
Okay! Time to show me your brilliance! What would the Answer Set produce?
Answer to Quiz – Fill in the Blank Values in the Answer Set
Sample_Table
| Cust_No | Acc_Balance | Location | ||
| 0 | ? | 3 |
Here is the answer set! How'd you do? The NULLIFZERO command found a zero in Cust_No so it made it Null. The others were not zero so they retained their value. The only time NULLIFZERO changes data is if it finds a zero, and then it changes it to null.
Quiz – Fill in the Answers for the NULLIF Command
Sample_Table
| Cust_No | Acc_Balance | Location | ||
| 0 | ? | 3 |
You can also use the NULLIF(). What you are asking Teradata to do is to NULL the answer if the COLUMN matches the number in the parenthesis. What would the above Answer Set produce from your analysis?
Quiz – Fill in the Answers for the NULLIF Command
Sample_Table
| Cust_No | Acc_Balance | Location | ||
| 0 | ? | 3 |
Look at the answers above and if it doesn't make sense go over it again until it does.
The ZEROIFNULL Command
This is the ZEROIFNULL. What it will do is put a zero into a place where a NULL shows up. What would the Answer Set produce?
Answer to the ZEROIFNULL Question
The answer set placed a zero in the place of the NULL Acc_Balance, but the other values didn't change because they were NOT Null.
The COALESCE Command
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone) as Phone
FROM Sample_Table ;
| Last_Name | Phone |
What would the answers be for all four rows?
Coalesce returns the first non-Null value in a list, and if all values are Null returns Null.
The COALESCE Answer Set
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone) as Phone
FROM Sample_Table ;
Coalesce returns the first non-Null value in a list, and if all values are Null returns Null.
The Coalesce Quiz
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone, ‘No Phone’)
as Phone
FROM Sample_Table ;
| Last_Name | Phone |
What would the answers be for all four rows?
Coalesce returns the first non-Null value in a list, and if all values are Null returns Null. Since we decided in the above query we don't want NULLs notice we have placed a literal ‘No Phone’ in the list. How will this effect the Answer Set?
Answers to the Coalesce Quiz
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone, ‘No Phone’)
as Phone
FROM Sample_Table ;
Answers are above! We put a literal in the list so there's no chance of NULL returning.
The Basics of CAST (Convert And STore)
CAST will convert a column or value's data type temporarily into another data type.
Below is the syntax:
SELECT CAST(<column-name> AS <data-type>[(<length>)] )
FROM <table-name> ;
Examples using CAST:
CAST ( <smallint-data> AS CHAR(5) ) /* convert smallint to character */
CAST ( <decimal-data> AS INTEGER ) /* truncates decimals */
CAST ( <byteint-data> AS SMALLINT ) /* convert binary to smallint */CAST ( <char-data> AS BYTE (128) ) /* convert character to binary */
CAST ( <byteint-data> AS VARCHAR(5) ) /* convert byteint to character */
CAST ( <integer-data> AS FLOAT ) /* convert integer to float point */
Data can be converted from one type to another by using the CAST function. As long as the data involved does not break any data rules (i.e. placing alphabetic or special characters into a numeric data type) the conversion works. The name of the CAST function comes from the Convert And STore operation that it performs.
Some Great CAST (Convert And STore) Examples
SELECT CAST(‘ABCDE’ AS CHAR(1) ) AS Trunc
,CAST(128 AS CHAR(3) ) AS OK
,CAST(127 AS INTEGER ) AS Bigger ;
| Trunc | OK | Bigger | ||
| A | 128 | 127 |
The first CAST truncates the five characters (left to right) to form the single character ‘A’. In the second CAST, the integer 128 is converted to three characters and left justified in the output. The 127 was initially stored in a SMALLINT (5 digits – up to 32767) and then converted to an INTEGER. Hence, it uses 11 character positions for its display, ten numeric digits and a sign (positive assumed) and right justified as numeric.
Some Great CAST (Convert And STore) Examples
SELECT CAST(121.53 AS SMALLINT) AS Whole
,CAST(121.53 AS DECIMAL(3,0)) AS Rounder ;
| Whole | Rounder | |
| 121 | 122 |
The value of 121.53 was initially stored as a DECIMAL as 5 total digits with 2 of them to the right of the decimal point. Then it is converted to a SMALLINT using CAST to remove the decimal positions. Therefore, it truncates data by stripping off the decimal portion. It does not round data using this data type. On the other hand, the CAST in the fifth column called Rounder is converted to a DECIMAL as 3 digits with no digits (3,0) to the right of the decimal, so it will round data values instead of truncating. Since .53 is greater than .5, it is rounded up to 122.
Some Great CAST (Convert And STore) Examples
SELECT Order_Number as OrdNo
,Customer_Number as CustNo
,Order_Date
,Order_Total
,CAST(Order_Total as integer) as Chopped
,CAST(Order_Total as Decimal(5,0)) as Rounded
FROM Order_Table ;
The Column Chopped takes Order_Total (a Decimal (10,2) and CASTs it as an integer, which chops off the decimals. Rounded CASTs Order_Total as a Decimal (5,0), which takes the decimals and rounds up if the decimal is .50 or above.
A Teradata Extension – The Implied Cast
SELECT <column-name> ( <data-type> [(<length>)]
FROM <table-name> ;
SELECT ‘ABCDE’ (CHAR(1)) AS Shortened
,128 (CHAR(3)) AS OOPS1
,-128 (CHAR(3)) AS OOPS2
,128 (INTEGER) AS Bigger
,121.13 (SMALLINT) AS Whole ;
This Teradata extension conversion is requested by placing the “implied’ data type conversion in parentheses after the column name. What happened in the column named OOPS1 and OOPS2? the value 128 is 1 greater than 127 and therefore too large of a value to store in a BYTEINT. So it is automatically stored as a SMALLINT (5 digits plus a sign) before the conversion. The implicit conversion changes it to a character type with the first 3 characters being returned. As a result, only the first 3 spaces are seen in the report (_ _ _ 128). Likewise, OOPS2 is stored as (_ _ –128) with the first three characters (2 spaces and – ) shown in the output.
The Basics of the CASE Statements
Sample_Table
| Course_Name | Credits | |
| Tera-Tom on SQL | 1 |
| Course_Name | Credits |
What will the answer be to this query?
This is a CASE STATEMENT, which allows you to do evaluate a column in your table and from that, come up with a new answer for your report. Ever CASE begins with a CASE and they all must end with a corresponding END. What would the answer be?
The Basics of the CASE Statement shown Visually
Sample_Table
| Course_Name | Credits | |
| Tera-Tom on SQL | 1 |
This is a CASE STATEMENT, which allows you to do evaluate a column in your table and from that, come up with a new answer for your report. Ever CASE begins with a CASE and they all must end with a corresponding END. What would the answer be?
Valued Case Statement Vs Searched Case Statement
The second example is better unless you have a simple query like the first example.
Valued Case Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN ‘One Credit’
WHEN 2 THEN ‘Two Credits’
WHEN 3 THEN ‘Three Credits’
Else ‘Credits not found’
END AS CreditAlias
FROM Course_Table ORDER BY 1 ;
Look at the CASE Statement and look at the Course_Table and fill in the Answer Set.
Valued Case Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN ‘One Credit’
WHEN 2 THEN ‘Two Credits’
WHEN 3 THEN ‘Three Credits’
Else ‘Credits not found’
END AS CreditAlias
FROM Course_Table ORDER BY 1 ;
| Course_Name | CreditAlias |
| Advanced SQL | Three Credits |
| Database Administration | Credits not found |
| Introduction to SQL | Three Credits |
| Physical Database Design | Credits not found |
| Teradata Concepts | Three Credits |
| V2R3 SQL Features | Two Credits |
Above is the full answer set.
Searched Case Statement
SELECT Course_Name as CCC
,CASE
WHEN Credits <= 1 THEN ‘One’
WHEN Credits = 2 THEN ‘Two’
WHEN Credits < 4 THEN ‘Three’
WHEN CCC like ‘Tera%’ Then ‘4’
Else ‘Don’'t know’
END AS CreditAlias
FROM Course_Table ORDER BY 1;
Look at the CASE Statement and look at the Course_Table and fill in the Answer Set.
Searched Case Statement
SELECT Course_Name as CCC
,CASE
WHEN Credits <= 1 THEN ‘One’
WHEN Credits = 2 THEN ‘Two’
WHEN Credits < 4 THEN ‘Three’
WHEN CCC like ‘Tera%’ Then ‘4’
Else ‘Don’'t know’
END AS CreditAlias
FROM Course_Table ORDER BY 1;
| CCC | CreditAlias |
| Advanced SQL | Three |
| Database Administration | Don't know |
| Introduction to SQL | Three |
| Physical Database Design | Don't know |
| Teradata Concepts | Three |
| V2R3 SQL Features | Two |
Above is the full answer set.
When NO ELSE is present in CASE Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN ‘One Credit’
WHEN 2 THEN ‘Two Credits’
WHEN 3 THEN ‘Three Credits’
END AS CreditAlias
FROM Sample_Table ;
Notice now that we have a 4 under the ‘Credit’ Column. However, in our CASE statement, we don't have instructions on what to do if the number is 4. What will occur?
When NO ELSE is present in CASE Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN ‘One Credit’
WHEN 2 THEN ‘Two Credits’
WHEN 3 THEN ‘Three Credits’
END AS CreditAlias
FROM Sample_Table ;
Since our value of 4 fell through the CASE statement without an ELSE statement a NULL value is returned in the Answer Set.
When an ELSE is present in CASE Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN ‘One Credit’
WHEN 2 THEN ‘Two Credits’
WHEN 3 THEN ‘Three Credits’
ELSE ‘Don'’t Know’
END AS CreditAlias
FROM Sample_Table ;
Notice now that we have a 4 under the ‘Credit’ Column. However, in our CASE statement, we don't have instructions on what to do if the number is 4. What will occur?
When NO ELSE is present in CASE Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN ‘One Credit’
WHEN 2 THEN ‘Two Credits’
WHEN 3 THEN ‘Three Credits’
ELSE ‘Don’'t Know’
END AS CreditAlias
FROM Sample_Table ;
Since our value of 4 fell through the CASE statement the ELSE statement kicked in and we delivered ‘Don't Know’. Notice two single quotes that provided the word Don't.
When an Alias is NOT used in a CASE Statement
Sample_Table
| Course_Name | Credits | |
| Tera-Tom on SQL | 4 |
Notice now that we don't have an ALIAS for the CASE Statement. What will the system place in there for the Column Title.
When an Alias is NOT used in a CASE Statement
Sample_Table
| Course_Name | Credits | |
| Tera-Tom on SQL | 4 |
Notice now that we don't have an ALIAS for the CASE Statement. The title given by default is < CASE Expression >. That is why you should ALIAS your Case statements.
When NO ELSE is present in CASE Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN ‘One Credit’
WHEN 2 THEN ‘Two Credits’
WHEN 3 THEN ‘Three Credits’
ELSE ‘Don’'t Know’
END AS CreditAlias
FROM Sample_Table ;
Since our value of 4 fell through the CASE statement the ELSE statement kicked in and we delivered ‘Don't Know'. Notice two single quotes that provided the word Don't.
Combining Searched Case and Valued Case
This Query above uses both a Valued Case and Searched Case. That's ALLOWED!
A Trick for getting a Horizontal Case
SELECT AVG(CASE Class_Code
WHEN ‘FR’ THEN Grade_pt
ELSE NULL END) (format ‘Z.ZZ’) AS Freshman_GPA
,AVG(CASE Class_Code
WHEN ‘SO' THEN Grade_pt
ELSE NULL END) (format ‘Z.ZZ’) AS Sophomore_GPA
,AVG(CASE Class_Code
WHEN ‘JR’ THEN Grade_pt
ELSE NULL END) (format ‘Z.ZZ’) AS Junior_GPA
,AVG(CASE Class_Code
WHEN ‘SR' THEN Grade_pt
ELSE NULL END) (format ‘Z.ZZ’) AS Senior_GPA
FROM Student_Table
WHERE Class_Code IS NOT NULL ;
Aggregates ignore Nulls so knowing this trick has allowed for Horizontal Reporting
Nested Case
SELECT Last_Name
,CASE Class_Code
WHEN ‘JR’ THEN ‘Jr ‘
||(CASE WHEN Grade_pt < 2 THEN ‘Failing’
WHEN Grade_pt < 3.5 THEN ‘Passing’
ELSE ‘Exceeding’
END)
ELSE ‘Sr ‘
||(CASE WHEN Grade_pt < 2 THEN ‘Failing’
WHEN Grade_pt < 3.5 THEN ‘Passing’
ELSE ‘Exceeding’
END)
END AS Status
FROM Student_Table WHERE Class_Code IN (‘JR','SR’)
ORDER BY Class_Code, Last_Name;
A NESTED Case occurs when you have a Case Statement within another CASE Statement. Notice the Double Pipe symbols (||) that provide Concatenation.
Put a CASE in the ORDER BY
