Code Colloquy

Curtis Copley
OTJ, Fall 1996

A Comprehensive Implementation of Conditional Logic Using SQL Expressions

SQL doesn't let you use IF-THEN-ELSE statements?! This article shows you step by step how to work around that nasty restriction.

SQL is a non-procedural language. It has no explicit loops, no IF-THEN-ELSE, and no CASE control structures. SQL has AND, OR, and NOT, but it doesn't have an exclusive OR function or various other logic constructs. SQL has a weird three-valued logic system - that is, if NULL can be called a value and if three values can be called logical. Therefore, when you need to use conditional logic, you probably end up using a "real language" like C.

I developed the ideas in this article while working on a "real program" in C, which had thousands of lines of code, ran for days at a time, and crashed for unknown reasons. The only tasks this program performed were fetch rows from the database, perform a set of tests on the values, set some flags, and update the rows in the database. I kept thinking that if only SQL had an IF statement, I could ditch the entire C program and replace it with a few UPDATE statements.

Fortunately, Oracle SQL does have an IF statement; it's called DECODE, and it is much more powerful than it first appears to be. Using DECODE, you can perform a straightforward translation of even the most complex IF statements into a corresponding set of DECODE operations. No procedural language is needed.

In SQL, you are limited in your use of comparisons and logical operators (such as AND, OR, and NOT) to within the WHERE clause, and constructs such as IF-THEN-ELSE are not available at all. However, sometimes you need to write an UPDATE statement (in pseudo-code). (See Table 1.)

Because SQL lacks IF-THEN-ELSE logic, programmers usually write such updates using a procedural language, such as C or Oracle's PL/SQL. Unfortunately, procedural languages introduce several problems. When writing an UPDATE statement in a procedural language, programmers must write SELECT and FETCH statements (and the supporting variable declarations and loops) in addition to the UPDATE statements. If you have to update 200,000 rows, then 200,000 fetches and 200,000 updates must be issued. Of course, 200,000 loops, fetches, and updates incur a substantial amount of processing overhead. But procedural languages suffer from more problems than just increased overhead.

Because Oracle PL/SQL is interpreted, its throughput is lower. C must be compiled and linked before execution and may not be portable across all platforms. You could try to get by with non-procedural SQL and use three UPDATE statements. Unfortunately, it is difficult to ensure that all of the rows are updated and that each row is updated only once. In addition, this process requires three UPDATE passes through the table, substantially increasing processing time.

Fortunately, there is an alternative to writing procedural code. In this article, I outline an approach that enables you to work around the limitations of SQL and perform conditional logic using SQL expressions. Implementing conditional logic directly in SQL lets you avoid the hassles and overhead that you incur when using a procedural language. Your source code will be shorter and, if written carefully, will run much faster. Unburdened by the interface to a procedural language, your code will also be easier to understand.

USING CONDITIONAL LOGIC

To help you better understand where this article is headed, I'll begin by breaking down conditional logic piece by piece. The most obvious part of conditional logic is the IF construct:

IF condition1 THEN action1
ELSIF condition2 then action2
ELSE action3

The conditions are composed of tests, combined together with logic operations:

(test1 OR test2) AND
(test3 OR test4)

The tests are made up of comparison operations, such as "x >y," and will return values indicating whether the result is TRUE, FALSE, or UNKNOWN (NULL in SQL).

A comprehensive implementation of conditional logic requires that you build a methodology from the foundation on up. Therefore, let's start with the representation of true and false and work our way back up through condition tests and logic operations until we reach the full IF-THEN-ELSE statement.

You need the following components to implement conditional logic:

• truth values
• condition tests
• logic operations
• IF-THEN-ELSE constructs

TRUTH VALUES

SQL does not support a Boolean data type. You must establish your own values for TRUE and FALSE. Computer tradition holds that TRUE is represented by 1 and FALSE is represented by 0. For the purposes of conditional logic, a more workable representation of FALSE is -1. This makes sense if you think of NOT as negation, and FALSE as the negation of TRUE. Throughout this article, I use arithmetic negation to implement logical negation. Therefore, the logic value TRUE will be represented by 1 and the logic value FALSE will be represented by -1. These two values establish the basis for the logic system.

SQL logic is complicated by a third possibility: NULL. However, NULL is virtually unsupported by SQL logic operators. There is no operator that will convert NULL to either TRUE or FALSE. In contrast, the system of logic proposed in this article is intended to be strictly two-valued.

It is true that NVL can be used to supply a default value for any SQL data type, but Boolean is not a SQL data type. SQL comes equipped with a single one-argument logic operator, NOT, and only a couple of two-argument logic operators: AND and OR. However, the number of available functions is determined by the equation, (V**(V**N)), where V is the number of values in the system and N is the number of arguments to the function. Thus, for a two-valued system, there are four possible one-argument operators (TRUE, FALSE, A, and NOT A) and 16 possible two-argument operators.

SQL has not only TRUE and FALSE, but UNKNOWN as well. That makes three possible values. Referring to the equation (V**(V**N)) again, the number of possible one-argument operators in a three-valued system is 27. Among the missing one-argument operators are things such as IS TRUE and is NOT UNKNOWN. These have been addressed to some degree in SQL-92 but have not yet been implemented in Oracle SQL. The number of possible two argument operators in a three-valued system is a colossal 19,683! I am not suggesting that SQL needs several thousand more operators (heaven forbid), but I do think SQL programmers deserve more than just AND and OR.

More to the point, the three-valued logic of SQL ignores a fact of life: A query either returns a row or it doesn't. There is no maybe. Logically, if I write:

SELECT empid FROM emp
WHERE deptno=100
UNION
SELECT empid FROM emp
WHERE NOT (deptno=100)

I should expect to get all the rows. Unfortunately, those of you well acquainted with SQL realize that rows having a null deptno will not be returned. This confusing situation is a source of countless bugs. Codd and Date have discussed this problem at great length in past issues of DBMS magazine, but I have thrown in the towel on three-valued logic and confined my code to using only TRUE and FALSE.

CONDITION TESTS

The preceding section established 1 and -1 as the values for TRUE and FALSE. The next issue concerns how to create condition tests that return only the values 1 and -1 (TRUE and FALSE). In the WHERE clause, you would write expressions such as X=Y or SCORE BETWEEN 0 AND 100. These expressions are allowed in the WHERE clause only. It will be necessary to substitute equivalent expressions that return only the values 1 and -1 to match the logic system. Table 2 shows how to create condition tests that return only these two values.

To understand Table 2, you must be familiar with Oracle's DECODE function. DECODE is explained in a number of places, including the Help facility in SQL*Plus. Its syntax is:

DECODE( expr, search, result [,
search, result] ... [, default] )

expr, search, result, and default may be character, date, number, rowid, and so on. They can be columns or expressions. If expr is not null, DECODE checks for the first search value equal to expr and returns the corresponding result. If expr is null, DECODE checks for the first search value that is null and returns the corresponding result. Please note: This is the only place in SQL where two nulls are equal. If there is no matching search value, DECODE returns the default value. If there is no default value, DECODE returns null.

The data type returned by DECODE is determined by the first result value. The data type of NULL is VARCHAR2. If the first result is null, the data type returned by decode will be varchar2. If your first return value is null, but you want to return a number, use TO_NUMBER(NULL). If your first return value is null, but you want to return a date, use TO_DATE(NULL).

Now, back to condition tests. There are two ways to implement things such as NOT LIKE and NOT BETWEEN. One approach is to reverse the "1" and "-1" values in the function. The other approach is to prefix the LIKE or BETWEEN expressions with "-" (NOT). This tactic avoids the need to create another version of the same expression, maintains consistency, and facilitates both readability and reusability of code fragments later. Table 3 shows condition tests that are based on the update pseudo-code example in Table 1.

The SIGN function deserves special attention for its usefulness with this system, because it has only four possible return values. Its syntax is SIGN(expr), where expr is any numeric expression. It evaluates the expression and returns 1 if the result is positive, -1 if negative, 0 if zero, and null if the result is null. The return values of 1 and -1 are handy because they are TRUE and FALSE in this logic system. For example, if hours_per_week is greater than 38, then:

SIGN(hours_per_week-38)

will return 1 (TRUE). If it is less than 38 the result will be -1 (FALSE). But be careful: If hours_per_week equals 38, the value will be zero. If it is null, the result will be null. These are not valid values in the logic system. Use extreme caution if you decide to omit the surrounding DECODE function: If you use the SIGN function later in a way that depends on only 1 or -1 being returned, you could be in for some very hard-to-track bugs.

Table 4 shows another use of the DECODE/SIGN combination. One of the nice things about spelling out all of the cases is that it becomes clear that you considered all four possibilities when you wrote the code. Table 5 shows an example based on the UPDATE pseudo-code in Table 1.

LOGIC OPERATIONS

Now that you can write condition tests, you need functions that can perform operations on the results of those expressions. The logic operators provided with SQL are NOT, AND, and OR. This is somewhat limited, however. SQL does not provide other logic operators, such as NAND, NOR, IFF, and XOR. "A NAND B" can be implemented by "NOT (A AND B)." NAND can be implemented by "NOT (A OR B)." IFF and XOR cannot be implemented in SQL using AND, OR, and NOT without redundantly specifying the expressions for A and B. Such redundancy is not required using this system, as illustrated by the examples in Table 6. These examples show how you can represent each of these operators as expressions. (Assume that a, b, and c are condition tests returning either 1 or -1, representing TRUE or FALSE, respectively.)

If a, b, or c is NULL, the result of any of the logic operations listed will also be NULL. If you write your condition tests as I specified earlier, you will not have any nulls to worry about. Table 7 shows an example of the OR operator, based on the UPDATE pseudo-code example in Table 1.

The LEAST and GREATEST functions take a list of arguments. Although only two conditions were OR'd together, it could as easily have been a dozen. These functions can also be nested inside one another. Using logic operations, you should be able to perform an almost mechanical translation of even the most hideously complex real-world condition.

IF-THEN-ELSIF-ELSE CONSTRUCTS

You've written condition tests and logic expressions; now you're ready to take on IF-THEN-ELSIF-ELSE statements. The following use of DECODE is a stylistic departure from the norm; usually DECODE is used for things such as testing whether a column is equal to any of a series of values. Each time you perform a different test, you must nest your DECODE functions one level deeper. With a long series of tests, the nested DECODE branches can become too deep for anyone to follow.

For the purpose of creating IF-THEN-ELSIF-ELSE type constructions, the normal use of DECODE is reversed. You are not testing whether a single test condition is TRUE; you are searching for the first test condition that evaluates to TRUE. The whole point of this style of logic is to handle much more complicated logic conditions. Any of the conditions (a, a1, a2, b, b1, b2) in the Table 8 might have been a highly complex logic expression, yet the DECODE functions are nested no deeper than an IF statement would be. The almost mechanical translation of IF statements afforded by this system keeps the nesting depth to a minimum.

In Table 8, assume a, a1, a2, b, b1, and b2 are condition tests that evaluate to either 1 or -1. Please note that this DECODE only checks for the truth value of 1. At this point, any other value (such as -1, 0, or NULL) is FALSE. However, if you need to perform the negation (NOT) of this code elsewhere in your programs, the negation will work only on -1. Zero and NULL will not be converted to TRUE. If you always restrict your logic values to 1 and -1, you can perform cut-and-paste editing much more safely.

PUTTING IT ALL TOGETHER

Now you are ready to solve the problem I gave you at the beginning of this article. Table 9 reveals the pieces to the puzzle. Table 10 shows the fully assembled puzzle.

LOOK, MA! NO LOOPS

When is it worthwhile to use this type of construction? It is definitely worthwhile when you have to update a dozen flags in each of 200,000 rows. Even if each flag was set based on entirely different conditions, they could all be set by a single UPDATE statement. No loops, no procedural code, no compilation. Using these techniques, a single SQL statement could be used to update a set of flags, even if the conditions become extremely complex.

Regardless of whether you are writing SELECT, INSERT, UPDATE, or DELETE statements, you can use this system of logic to obtain efficiency, reliability, and portability, without resorting to writing procedural code. That makes all of this worthwhile.

TABLE 1

Pseudo code

UPDATE employee -- set overtime pay rate
 IF salaried='Y' THEN -- handle 1987 policy change
 IF hire_date < 1987 THEN
 otrate=1.0; -- straight hourly rate
 ELSE
 otrate=0.0; -- no overtime pay
 END IF;
 ELSIF (hours_per_week > 38) OR (manager_override='Y') THEN
 otrate=1.5; -- time and a half
 ELSE
 otrate=1.0; -- straight hourly rate
 END IF;

TABLE 2

Operator
Equivalent code
Comments
x=y
DECODE(x,y,1,-1)
Works on all data types

True if both are null

False if only one null
x!=y
-DECODE(x,y,1,-1)
Works on all data types

False if both are null

True if only one null
x>y
DECODE(SIGN(x-y),1,1,-1)
Use on number or date

False if x<=y

False if either is null
x>=y
DECODE(SIGN(x-y),1,1,0,1,-1)
Use on number or date False if x False if either is null
x BETWEEN y AND z
DECODE(x,GREATEST(y, LEAST(z,x)),1,-1)
Works on all data types
x IS NULL
DECODE(x,NULL,1,-1)
Works on all data types

x IS NOT NULL
-DECODE(x,NULL,1,-1)
Works on all data types
ssn LIKE

'___-__-____'
DECODE(TRANSLATE(ssn, '0123456789', '9999999999'), '999-99-9999', 1, -1)
Better than LIKE for SSN, because it tests for numbers; the same approach
works for alphabetics, too
grade IN

('A','B','C')
DECODE(grade, 'A',1,'B',1,'C', 1,-1)
Works on all data types
EXISTS(subqry)
DECODE(t.rowid,NULL,-1,1)
Redo subqry as Outer Join

TABLE 3

Pseudo code
Equivalent code
salaried='Y'
DECODE(salaried,'Y',1,-1)
manager_override='Y'
DECODE(manager_override,'Y',1,-1)
hours_per_week>38
DECODE(SIGN(hours_per_week-38),1,1,-1)

TABLE 4

Conditional code
DECODE/SIGN version
DECODE(SIGN(x-y),
IF x>y THEN RETURN 'GREATER';
1, 'GREATER',
ELSIF x=y THEN RETURN 'EQUAL';
0, 'EQUAL',
ELSIF x -1, 'LESS',
 
ELSE RETURN 'UNKNOWN';
'UNKNOWN')

TABLE 5

Pseudo code
Equivalent code
IF hire_date<1987
DECODE(SIGN(hire_date-TO_DATE('01-JAN-87')),

1, 0.0, -- hire_date > 1987

0, 0.0, -- hire_date = 1987
THEN otrate=1.0;
-1, 1.0, -- hire_date < 1987
ELSE otrate=0.0;
0.0 -- hire_date IS NULL
END IF;
)

TABLE 6

TABLE 7

Pseudo code
Equivalent code
hours_per_week>38
GREATEST(
OR
DECODE(SIGN(hours_per_week-38),1,1,-1),
manager_override='Y'
DECODE(manager_override,'Y',1,-1))

TABLE 8

Procedural code
Equivalent code
IF
DECODE(1,
a THEN RETURN 'r1';
a, 'r1',
ELSIF b THEN RETURN 'r2';
b, 'r2',
ELSE RETURN 'r3';
'r3')
IF
DECODE(1,
a THEN IF
a, DECODE(1,
a1 THEN RETURN 'r1';
a1, 'r1',
ELSIF a2 THEN RETURN 'r2';
a2, 'r2',
ELSE RETURN 'r3';
'r3'
END IF;
),
ELSIF b THEN IF
b, DECODE(1,
b1 THEN RETURN 'r4';
b1, 'r4',
ELSIF b2 THEN RETURN 'r5';
b2, 'r5',
ELSE RETURN 'r6';
'r6'
END IF;
),
ELSE RETURN 'r7';
'r7'
END IF;
)

TABLE 9

Pseudo code
Equivalent code
salaried='Y'
DECODE(salaried,'Y',1,-1)
IF hire_date<1987	
DECODE(SIGN(hire_date-TO_DATE('01-JAN-87')),

1, 0.0, -- hire_date >1987

0, 0.0, -- hire_date = 1987
THEN otrate=1.0;
-1, 1.0, -- hire_date <1987
ELSE otrate=0.0;
0.0 -- hire_date IS NULL
END IF;
)
hours_per_week>38	
DECODE(SIGN(hours_per_week-38),1,1,-1)
manager_override='Y'
DECODE(manager_override,'Y',1,-1)
GREATEST(
hours_per_week>38 OR manager_override='Y'
DECODE(SIGN(hours_per_week-38),1,1,-1),

DECODE(manager_override,'Y',1,-1))

IF
DECODE(1,
salaried='Y'
DECODE(salaried,'Y',1,1),
THEN
IF hire_date<1987	
DECODE(SIGN(hire_date-TO_DATE('01-JAN-87')),

1, 0.0, -- hire_date >1987

0, 0.0, -- hire_date = 1987
THEN otrate=1.0;
-1, 1.0, -- hire_date <1987
ELSE otrate=0.0;
0.0 -- hire_date IS NULL
END IF;
)
ELSIF

hours_per_week>38 OR manager_override='Y'
GREATEST(

DECODE(SIGN(hours_per_week-38),1,1,-1),
THEN otrate=1.5;
DECODE(manager_override,'Y',1,-1)),
ELSE otrate=1.0;
1.5,
END IF;
1.0
)

TABLE 10

Equivalent code

UPDATE employee
SET otrate=
 -- begin overtime pay rate setting logic
 DECODE(1,
 -- IF salaried
 DECODE(salaried,'Y',1,-1),
 -- hired before 1987?
 DECODE(SIGN(hire_date-TO_DATE('01-JAN-87')),
 1, 0.0, -- hire_date > 1987
 0, 0.0, -- hire_date = 1987
 -1, 1.0, -- hire_date < 1987
 0.0 -- hire_date IS NULL
 ) -- end hired before 1987
 -- ELSIF full-time or management override
 GREATEST( -- begin OR clause
 DECODE(SIGN(hours_per_week-38), 1, 1, -1), -- hrs > 38
 DECODE(manager_override, 'Y', 1, -1) -- mgt approved anyway
 ), -- end OR clause
 1.5, -- IF full-time or mgt override, pay time and a half
 -- ELSE (no other cases applied)
 1.0 -- pay straight overtime
 ) -- end overtime pay rate setting logic