The SQL language as implemented by Informix prior to IDS 7.3 is technically a non-procedural language. "Pure" SQL does not include the types of programming statements that programmers use to write computer programs. Such statements as "If-Then," "Do While," and "Case" are not present in SQL. If you are interested in writing procedural code, therefore, the SQL language has not been your tool of choice. Instead, you may have used various workarounds to achieve procedural results.
One method uses Informix's SPL (Stored Procedure Language) to write stored procedures that contain non-procedural elements. Other approaches have included writing embedded SQL code using one of the ESQL products. Still other methods, such as doing multiple SELECTs and UNIONs, can approximate procedural programming, but all of these methods are complex and highly error-prone.
A more common method is to use complex JOIN statement to join parameter tables and return data by SELECTING from the JOINed tables. While these methods do work, they often make it difficult for subsequent programmers to decipher the data flow without good documentation. At worse, future programmers will need to check out the data elements and data contents of multiple tables in order to figure out what the code is doing. Often the more simple, inline code of a procedural language is easier to follow and debug.
With the release of the 7.3X versions of Informix Dynamic Server, you now have several new commands that can simplify your coding jobs and allow you to do some tasks in a simpler, more obvious manner than before.
In this 10-Minute Solution, I'll show you a few of the new expressions and functions supported by IDS 7.3, so you can see how they can make your tasks easier.
CASE Expression
One of the new constructs is the CASE expression. This is an expression that can be selected or manipulated just like all of the other expressions in SQL. The following example uses the "state" table from the stores7 database. This table contains two columns—a "code" column that is a two-character state abbreviation and a "sname" column that contains the name of the state:
SELECT
CASE code
WHEN 'AK' THEN "Alaska"
WHEN 'HI' THEN "Hawaii"
ELSE "Unknown"
END,
code
FROM state
This SQL statement returns:
(expression) code
Alaska AK
Hawaii HI
.........
Here, the CASE expression begins with the word "CASE" and is completed with the word "END." Everything between these two words is an expression that will be returned from the database in the SELECT statement. Notice that because everything from CASE to END is an expression, you need to place a comma after the END if you want to select anything else from the database after the CASE statement.
The system tests for a true condition in each WHEN clause and returns the appropriate value as specified in the THEN clause. The testing of WHEN clauses occurs in the order in which they are presented. In the following example, only the first "AK" is found:
SELECT
CASE code
WHEN 'AK' THEN "Alaska"
WHEN 'HI' THEN "Hawaii"
WHEN 'AK' THEN "CODE DOES NOT GET HERE"
ELSE "Unknown"
END,
code
FROM state
This SQL statement returns the same result as the first because only the first WHEN 'AK' is executed.
If none of the WHEN clauses is found to be true, the CASE will return the value specified in the ELSE clause. If there's no ELSE clause, it returns null.
The CASE expression is useful in creating and dealing with code tables and lookup tables. For example, suppose that you had a system in which you were creating orders and then using a tax table to calculate applicable taxes, which vary by state. You would have an orders table that includes a code for the state. You would also have a tax table that looks something like this:
CREATE TABLE tax_rate (state_code char(2), tax_percentage integer);
Without using the advanced SQL statements, a simple query to apply sales tax to a transaction would look something like:
SELECT order.amount, tax_rate,tax_percentage
FROM order, tax_rate
WHERE order.state = tax_rate.state_code;
Using the CASE construct, you'd use a statement like this:
SELECT
Amount,
CASE state
WHEN 'AK' then ".08"
WHEN 'HI' then ".12"
END
FROM orders;
This is just an example and you probably wouldn't really hard-code the rates into an application. At this level of simplicity, the JOIN may actually be more readable that the CASE statement. However, as the calculation gets more complex and you have several different situations to consider (e.g., no state tax, other taxes, whether or not taxes are chargeable from your state, whether there's a tax exemption, etc.), the CASE statement begins to be more useful.
DECODE Function
The DECODE statement performs an action similar to the CASE expression mentioned above. DECODE has been around in Oracle for years; it allows the SQL statement to return different results based upon values found in a column. Here's an example:
SELECT code, DECODE(code,
'AK', "Alaska",
'AL', "Alabama",
'CA', "California")
from state
This statement returns:
code (expression)
AK Alaska
AL Alabama
Note that there are several syntactical differences between this and the CASE expression. DECODE is a function, while CASE is an expression. The term "DECODE(whatever...)" serves as a function to return a value from the database. Note that the function body must be enclosed in parentheses.
The term "code" in the segment "DECODE(code...)" refers to the column that is being evaluated—in this instance, the code column. After each code:decoded pair, you need a comma, unlike the CASE statement that doesn't require commas after each WHEN clause.
There are several reasons that you might want to use a DECODE rather than a CASE expression. Although this is just my personal opinion, I think the DECODE format is somewhat more compact and easier to read than the CASE expression. In addition, DECODE is a function handled at the server level, whereas CASE must be parsed first at the client level. This could possibly give DECODE a bit of a performance edge over CASE. On the other hand, if you have a bunch of C programmers in your shop, the CASE statements may be much more familiar and readable. Different strokes. At least we now have the choice.
NVL Function
The NVL (null value) function allows the SQL statement to return more meaningful values when it encounters a null value in the column. For example, assume that a table "member" that has two columns: "name" and "address." Some of the rows do not include an address. The following output results from the SQL statement "SELECT name, address from member;":
name address
Lumbley Dallas, Texas
Murchie Middlebury, Vermont
Mystery Man
Here, Mystery Man does not have an address in the database. To make the output more readable, the following SQL statement uses the NVL function:
SELECT name, NVL(address,"Unlisted Address") as address from member;
This SQL statement returns the following:
name address
Lumbley Dallas, Texas
Murchie Middlebury, Vermont
Mystery Man Unlisted Address
With the NVL function, if the first argument has a non-null value, this non-null value is returned. If the first argument is null, the second value is returned.
