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 | |
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.
Cool, that's what php's spaceship operator does!
ReplyDelete$totalorder = ($decimal_number <=> $numeric_number);