SQL Scalar Function to make comparisons easier

This is another SQL Scalar Function I found almost by accident. The TOTALORDER scalar function allows me to compare two numbers and returns an integer to tell me which one is greater:

Comparison	Returned
Value 1 < Value 2	-1
Value 1 = Value 2	0
Value 1 > Value 2	1

The syntax for this Scalar Function is:

TOTALORDER(Value_1, Value_2)

To show how this works I am going to need a DDL Table or DDS File to have data to compare. I chose to build a DDL Table:

01 CREATE TABLE MYLIB.TESTTABLE ( 02 SOME_VALUE VARCHAR(10), 03 DECIMAL_NUMBER DECIMAL(5,2), 04 NUMERIC_NUMBER NUMERIC(5,2), 05 INTEGER_NUMBER SMALLINT 06 ) ;

I decided to have the three numeric columns be different types of numbers. Converting this from SQL definitions to the more standard IBM i ones these are:

SQL	IBM i
Decimal	Packed
Numeric	Zones
Small integer	Binary

I only need one row of data, therefore, I added the one row to my Table. I can use the following statement to see the row I inserted:

01 SELECT * FROM TESTTABLE SOME_ DECIMAL NUMERIC INTEGER VALUE _NUMBER _NUMBER _NUMBER ----- ------- ------- ------- FIRST 12.34 56.78 8

I can now use the TOTALORDER Scalar Function to compare the values in the various columns:

01 SELECT A.*, 02 TOTALORDER(DECIMAL_NUMBER,NUMERIC_NUMBER), 03 TOTALORDER(DECIMAL_NUMBER,INTEGER_NUMBER), 04 TOTALORDER(NUMERIC_NUMBER,INTEGER_NUMBER) 05 FROM TESTTABLE A SOME_ DECIMAL NUMERIC INTEGER VALUE _NUMBER _NUMBER _NUMBER 00005 00006 00007 ----- ------- ------- ------- ------ ------ ------ FIRST 12.34 56.78 8 -1 1 1

Line 1: I have selected all the columns in the table, which are the named columns in the results.

Line 2: I have compared the decimal and numeric values. As the numeric value is greater -1 is returned, see the fifth column.

Line 3: The decimal number is greater than the integer, therefore, the sixth column is 1.

Line 4: The numeric number is also greater than the integer, and the seventh column is 1.

The only result I have not generated when the two values are the same:

01 SELECT DECIMAL_NUMBER, 02 TOTALORDER(DECIMAL_NUMBER,DECIMAL_NUMBER) 03 FROM TESTTABLE DECIMAL _NUMBER 00002 ------- ------ 12.34 0

Line 2: A value in a column in one row will always be the same as itself. Therefore, a value of zero is returned, which is shown in the second column.

How would I use this Scalar Function?

I can see myself using it in a CASE statement, generating a new column based on others.

Below is an extremely simple of doing this:

01 SELECT A.*, 02 CASE TOTALORDER(DECIMAL_NUMBER,NUMERIC_NUMBER) 03 WHEN -1 THEN 'Dec < Num' 04 WHEN 1 THEN 'Dec > Num' 05 ELSE 'Dec = Num' 06 END, 07 CASE TOTALORDER(DECIMAL_NUMBER,INTEGER_NUMBER) 08 WHEN -1 THEN 'Dec < Int' 09 WHEN 1 THEN 'Dec > Int' 10 ELSE 'Dec = Int' 11 END, 12 CASE TOTALORDER(INTEGER_NUMBER,INTEGER_NUMBER) 13 WHEN -1 THEN 'Int < Int' 14 WHEN 1 THEN 'Int > Int' 15 ELSE 'Int = Int' 16 END 17 FROM TESTTABLE A

Lines 2 - 6: This new column will contain the text from the comparison between the decimal and numeric columns. What makes this so easy to type and understand is that I can place the Scalar Function next to the CASE and then all I need to give is the returned value and the result.

Lines 7 – 11: The next new column is derived from the comparison between the decimal and integer numbers.

Lines 12 – 16: This column is here to shows what happens when two values are the same.

My results are:

SOME_ DECIMAL NUMERIC INTEGER VALUE _NUMBER _NUMBER _NUMBER ----- ------- ------- ------- FIRST 12.34 56.78 8 00005 00006 00007 --------- --------- --------- Dec < Num Dec > Int Int = Int

Do I really need to explain the results? I am sure you understand why the values in these new columns are what they are.

If I was to do the same as the above statement without using the TOTALORDER it would look something like:

01 SELECT A.*, 02 CASE 03 WHEN DECIMAL_NUMBER < NUMERIC_NUMBER THEN 'Dec < Num' 04 WHEN DECIMAL_NUMBER > NUMERIC_NUMBER THEN 'Dec > Num' 05 ELSE 'Dec = Num' 06 END, 07 CASE 08 WHEN DECIMAL_NUMBER < INTEGER_NUMBER THEN 'Dec < Int' 09 WHEN DECIMAL_NUMBER > INTEGER_NUMBER THEN 'Dec > Int' 11 ELSE 'Dec = Int' 12 END, 13 CASE 14 WHEN INTEGER_NUMBER < INTEGER_NUMBER THEN 'Int < Int' 15 WHEN INTEGER_NUMBER > INTEGER_NUMBER THEN 'Int > Int' 16 ELSE 'Int = Int' 17 END 18 FROM TESTTABLE A

While the Case statements are still understandable, I prefer the look of the ones where I used the TOTALORDER Scalar Function.

 

You can learn more about the TOTALORDER SQL Scalar Function from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.