## Wednesday, December 20, 2023

### 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.

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

#### 1 comment:

1. Cool, that's what php's spaceship operator does!
\$totalorder = (\$decimal_number <=> \$numeric_number);

To prevent "comment spam" all comments are moderated.
```