The ANSI SQL-2 definition contains a case expression as part of the language. This allows the output of an expression to be vary according to the value of other expression. Microsoft Access also performs a similar operation with its IIF() function. Unfortunately, many existing systems do not support the current SQL language standard. If the test being performed is simply a test for null value, then IsNull() will suffice. For more complicated expressions, the expression is often implemented using temporary tables or UNIONs. These have the undesirable side effects of being extremely computationally expensive, and eliminate the use of the query as the basis of a view. There is a work-around, however.

If the cases sought can be changed into numeric values, the logical and arithmetic operators of SQL can come to your rescue. By forming expressions that will have a 0-value if they are not the desired case, you only need to add the expressions to simulate a case statement. Changing your cases into numeric values is often not as difficult as it seems — you just need to look at your data in a different way. The following examples are implemented in Sybase System X Transact-SQL (which does not implement the CASE statement), but have equivalents on most other platforms.

For our first example, let’s say you want to automatically generate pass/fail status from a test score. We will implement this with a combination of the substring() and sign() functions. Our expression:

substring("Fail",1,255*sign(1-sign(score-50))) + substring("Pass",1,255*sign(1+sign(score-51)))

The sign() returns the sign of a numeric value: +1 for positive values, 0 for zero values, -1 for negative values, and null for null values. Let’s examine the first half of the expression with a score of 75.

Expression	Value
score-50	25
sign(score-50)	1
1-sign(score-50)	0
sign(1-sign(score-50))	0
255*sign(1-sign(score-50))	0
substring(“Fail”,1,255*sign(1-sign(score-50)))	””

Now let’s look at the second half of the expression, again with our value of 75.

Expression	Value
score-51	24
sign(score-51)	1
1+sign(score-51)	2
sign(1+sign(score-51))	1
255*sign(1+sign(score-51))	255
substring(“Pass”,1,255*sign(1+sign(score-51)))	“Pass”

Combine these two expressions using the string concatenation operator, and we have “” + “Pass”, or simply, “Pass”. Note what we’ve discovered here. When dealing with numeric values, sign(1-sign(val1-val2)) is 0 if val1 is greater than val2, and 1 if val1 is less than or equal to val2. Similarly, sign(1+sign(val1-val2)) is 1 if val1 is greater than or equal to val2, and 0 if val1 is less than val2. Note that val1 and val2 can be column identifiers, static numeric values, or other expression. You can implement the complete set of comparison operators using abs() and sign() as show the following expressions:

Comparison	Expression
Val1 = val2	1-abs(sign(val1 - val2))
Val1 > val2	1-sign(1-sign(val1 - val2))
Val1 >= val2	sign(1+sign(val1 - val2))
Val1 < val2	1-sign(1+sign(val1 - val2))
Val1 <= val2	sign(1-sign(val1 - val2))
Val1 <> val2	abs(sign(val1 - val2))

In all of these expressions, the result is a value of 1 if the comparison is true, 0 if the comparison is false, and null if the comparison is null.

For our next example, we’ll use a more complicated set of cases. Let’s say you want to assign letter grades to the test scores: an A for 90+; B for 80-89; C for 70-79; D for 60-69; and an F for 59 and below. Pretty generous grades, I might add. The expression to generate our desired results:

substring("F",1,255*sign(1-sign(score-59))) +

substring("D",1,255*sign(1+sign(score-60))*sign(1-sign(score-69))) +

substring("C",1,255*sign(1+sign(score-70))*sign(1-sign(score-79))) +

substring("B",1,255*sign(1+sign(score-80))*sign(1-sign(score-89))) + substring("A",1,255*sign(1+sign(score-90)))

Notice that for the middle ranges, we are using the multiplication of two expressions. This is the same as saying x>=val1 and x<=val2. Using our numeric expressions, we can perform logical operations as well.

Comparison	Expression
Expr1 AND Expr2	(Expr1 * Expr2)
Expr1 OR Expr2	sign(Expr1 + Expr2)
NOT Expr2	(1 - Expr2)

When you use the sign() function to transform expressions into numeric ones, the door is opened for all sorts of comparisons to be performed. Any SQL function returning a numeric result can be used in these sort of expressions in place of val1 - val2.

In our next example, we will vary the results based on a date comparison. If today’s date is greater than the column due_date, we want amount_due increased by 5. The following expression will retrieve the desired results.

amount_due + (5 * (1-sign(1-sign(datediff(dd,due_date,getdate()))))

Or we can use this technique to change the formatting of data based on it’s content. In this example, we will format the column create_date differently if it is not the current year.

substring(

   datename(month,create_date) + " " + datepart(dd,create_date),

   1,

   255 * (1-abs(sign(datepart(yy,getdate()) - datepart(yy,create_date))))

+

substring(

   convert(char(12),create_date,101),

   1,

   255 * abs(sign(datepart(yy,getdate()) - datepart(yy,create_date))))

If create_date was July first of the current year, the expression results in “July 1”. But if it was July first of some other year, it would be in the format MM/DD/YYYY. Notice our comparisons were 1-abs(sign(val1 - val2)) and abs(sign(val1 - val2)), the comparisons for equality and inequality. Except datepart(yy,getdate()), yielding the current year, is being used for val1, and datepart(yy,create_date), yielding the year of create_date, is being used for val2.

Credit

Thanks, Bippin!