Chapter 12 – Interrogating the Data

"I don't know who my grandfather was. I am more interested in who his grandson will become."

Abraham Lincoln

NVL Syntax

Compatibility: NPS Extension

The NPS database software provides a function to substitute a NULL value in a column for another value. Earlier in this book we saw IS NULL and IS NOT NULL used within the WHERE clause. An additional way to test is to use the NVL function.

The purpose of this function is to compare the data value in the first column and when it contains a NULL, transform it for the life of the SQL statement to the second value.

The syntax for the NVL follows:

SELECTNVL(<column-name>, <column-or-literal-value> )

,<Aggregate>  ( NVL(<column-name>, <column-or-literal-value>  ))

FROM  <table-name>

GROUP BY 1 ;

The best use of the NVL is in a mathematics formula. In an earlier chapter, it was seen that anytime a NULL is used in math, the answer is a NULL. Therefore, the NVL can convert a NULL to a zero so that an answer is returned.

NVL Example

Here, literals are used to demonstrate the operation of the NVL function:

1 Row Returned

In the above SQL and its output:

•Col1 the value 0 is not a NULL, so the 0 is returned.

•Col2, the NULL is a NULL, so a 0 is returned.

•Col3, the NULL is a NULL, so the 3 is returned.

•Col4, the 3 is not a NULL, so the 3 is returned.

You can see from the example above (using literals) that if the NVL function finds a null value in the first parameter, then it will replace that null value with the second parameter. If the NVL function does not find a null value in the first parameter it will merely return the first character.

NVL Is Often Used With Calculations

The next SELECT shows what happens when a zero ends up in the calculation and then, how to avoid it using the NVL:

The above examples show the query and result set when the NVL is not used and then when it is used.

Comparisons of NVL

The following two examples that are used here to show the contrast:

The above examples show the query and result set when the NVL is not used and then when it is used.

A Real-World NVL Example

The next SELECT displays the student's last name and GPA. However, if there is no GPA, it includes the phrase "Missing GPA" in the output using the NVL to search Grade points for a NULL:

The above examples show the query and result set when the NVL checks for a null value on Class_Code. Any student who has a Class_Code that is NULL will see the message "Missing Class" on the report.

NVL2 Syntax

Compatibility: NPS Extension

The NPS database provides the user another way to test for NULL using the NVL2 function. The purpose of this function was to compare the data value in the first column for a NULL and when found, it returns the value in the third position value. If the value of the first position is not null, then the second value is returned. In this function, the first value is never returned.

The following syntax shows two different uses of the NVL2 function, first on a column and then within an aggregate:

SELECT NVL2(<column-name>,<column-or-value>,<column-or-value> )

                                     ,<Aggregate> ( NVL2(<column-name> ) )

FROM    <table-name>

GROUP BY 1;

NVL2 compares the data value in the first column for a NULL and when found, it returns the value in the third position value. If the value of the first position is not null, then the second value is returned. In this function, the first value is never returned. You can use this on a literal value, a column, or an aggregate.

NVL2 Example

The next SELECT uses literal values to
demonstrate the functionality of the NVL2:

In the above SQL and its output:

•   Col1 the value 0 is not NULL, so the 3 is returned.

•   Col2, the value 3 is not NULL, so the 0 is returned.

•   Col3, the NULL is NULL, so the 3 is returned.

•   Col4, the NULL is NULL, so the 0 is returned.

You can see from the example above (using literals) that if the NVL2 function finds a null value in the first parameter, then it will replace that null value with the third parameter. If the NVL2 function does not find a null value in the first parameter, it will replace that value with the second parameter.

NVL2 Syntax

Compatibility: NPS Extension

The NPS database provides the user another way to test for NULL using the NVL2 function. The purpose of this function was to compare the data value in the first column for a NULL and when found, it returns the value in the third position value. If the value of the first position is not null, then the second value is returned. In this function, the first value is never returned.

The following syntax shows two different uses of the NVL2 function, first on a column and then within an aggregate:

SELECT NVL2(<column-name>,<column-or-value>,<column-or-value> )

                                     ,<Aggregate> ( NVL2(<column-name> ) )

FROM   <table-name>

GROUP BY 1;

NVL2 compares the data value in the first column for a NULL and when found, it returns the value in the third position value. If the value of the first position is not null, then the second value is returned. In this function, the first value is never returned. You can use this on a literal value, a column, or an aggregate.

A Real-World NVL2 Example

The next SELECT displays the student's last name and GPA. However, if there is no GPA, it includes the phrase "Missing GPA" in the output using the COALESCE to search Grade points for a NULL:

The above examples show the query and result set when the NVL2 checks for a null value on Class_Code. Any student who has a Class_Code that is NULL will see the message "Missing Class" on the report.

DECODE Syntax

Compatibility: ANSI

As handy as NVL2 is, it only converts a zero to a NULL. Like its predecessor, the newer ANSI standard DECODE function also can convert a zero to a NULL. However, it can convert anything to a NULL. To use the DECODE, the SQL must pass the name of the column to compare and the value to compare for equal. Multiple compares are allowed, and if none of the compares are true, the default value is returned.

The following is the syntax for using the DECODE function.

   SELECT   DECODE(<column-name>, <data-value>, <result-value>

                                                    [ …, <data-value>, <result-value>]

                                         <default-value> )

                     FROM   <table-name>

                     GROUP BY 1 ;

The DECODE function also can convert a zero to a NULL. However, it can convert anything to a NULL.

DECODE Example

To show the operation of the DECODE, literal values are shown in the next example:

SELECT   DECODE(0, 0, NULL,0)  AS Col1

                  ,DECODE(0, 3, 2, 0)  AS Col2

                  ,DECODE(3, 0, null, 4)  AS Col3

                  ,DECODE(3, 3, NULL,3)  AS Col4

                  ,DECODE(NULL, 0, NULL, 3)  AS Col5 ;

In the above SQL and its output:

•   Col1 the value 0 was equal to 0, so a NULL is returned.

•   Col2, the 0 is not equal to a 3, so the 0 is returned.

•   Col3, the 3 is not equal to 0, so the 4 is returned.

•   Col4, the 3 is equal to 3, so a NULL is returned.

•   Col5, the NULL is not equal to 0, so the 3 is returned.

The above examples show the query and result set for the DECODE command. If the first parameter is equal to the second parameter, then the third entry is brought back. If the first parameter is NOT equal to the second parameter, then the fourth parameter is brought back.

A Real-World DECODE Example

The DECODE is great for situations when the SQL is doing comparisons, if a need arises to replace a code with another value.

An example of using the DECODE in comparing follows:

Let me explain. If the Dept_No column is equal to a 100, then put the word 'MRKT' in the DECODE column. If the Dept_No column is NOT equal to 100, then check to see if the Dept_No column is equal to 400. If Dept_No is equal to 400, then put in the words 'Customer Support'. If the Dept_No column is NOT 100 or 400, then put in 'Not Compared'.

Quiz – Fill in the Answers for the NULLIF Command

Fill in the Answer Set above after looking at the table and the query.

You can also use the NULLIF(). What you are asking Netezza to do is to NULL the answer if the COLUMN matches the number in the parentheses. What would the above Answer Set produce from your analysis?

Quiz – Fill in the Answers for the NULLIF Command

Look at the answers above, and if it doesn't make sense, go over it again until it does.

The COALESCE Command

Fill in the Answer Set above after looking at the table and the query.

Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.

The COALESCE Answer Set

Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.

The Coalesce Quiz

Fill in the Answer Set above after looking at the table and the query.

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?

Answer – The Coalesce Quiz

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)

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

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

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

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.

Round Function

SELECT salary

              ,ROUND(salary) "Int Salary"

              ,ROUND(salary,0) "Int Salary too"

              ,CAST(salary as integer)

              ,CAST(salary as decimal(7,0))

              ,ROUND(salary,1) "Int Salary"

FROM Employee_table;

Using this function, the data is automatically rounded to an integer when the option decimal places are not specified. Otherwise, the second value indicates how many decimal places to use in the precision of the answer.

Round Function Continued

SELECT salary

                 ,ROUND(salary) "Int Salary"

                 ,ROUND(salary,0) "Int Salary too"

                 ,CAST(salary as integer)

                 ,CAST(salary as decimal(7,0))

                 ,ROUND(salary,1) "Int Salary"

FROM Employee_table;

Above is an example of the rounding function. Using this function, the data is automatically rounded to an integer when the option decimal places are not specified.

The Basics of the CASE Statements

Fill in the Answer Set above after looking at the table and the 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. Every 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

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. Every CASE begins with a CASE, and they all must end with a corresponding END. What would the answer be?

Valued Case Vs. A Searched Case

The second example is better unless you have a simple query like the first example.

Quiz - Valued Case Statement

Fill in the Answer Set above after looking at the table and the query.

Look at the CASE Statement and look at the Course_Table, and fill in the Answer Set.

Answer - Valued Case Statement

Above is the full answer set.

Quiz - Searched Case Statement

Fill in the Answer Set above after looking at the table and the query.

Look at the CASE Statement and look at the Course_Table, and fill in the Answer Set.

Answer - Searched Case Statement

Above is the full answer set.

Quiz - When NO ELSE is present in CASE Statement

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?

Answer - When NO ELSE is present in CASE Statement

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

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

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

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

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.

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

Aggregates ignore Nulls, so knowing this trick has allowed for Horizontal Reporting. This isn't one CASE statement, but four separate CASE statements. Each statement checks for one particular Class_Code, so each row is analyzed four times. Three of those times will result in a NULL (no harm no foul because NULLs are ignored in the AVG).

Nested Case

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