Have you ever wanted the order of your data sorted differently based on some condition? You could do it by coding multiple cursors or creating dynamic SQL. Here is a way to do it with static SQL and only one cursor.
When I stumbled upon this tip I was actually trying to build the order by clause in a host variable and construct the host variable based on some program logic. I couldn't get that to work correctly, so I tried the method I described in my tip and it worked. I had never seen this in a book before.
There may be more efficient ways of doing this, since you are bringing back extra data in your result set. However, this is definitely a way of writing the SQL and limiting your logic to one cursor. I ran this on DB2 version 7.
You have the following table:
Name - Char(25) Addr - Char(30) SSN - Integer Age - Smallint Rec-Type - Char(2)The requirement is that your query must return all the information on the table in a certain order.
If the parameter provided is "CH", this indicates that the records being selected are children and must be returned in name/address order.
If the parameter provided is "AD", this indicates that the records being selected are adults and must be returned in SSN/NAME order.
If the parameter provided is "SC", this indicates that the records being selected are senior citizens and must be returned in AGE/SSN order.
Here is the way that this can be done with one cursor:
WS-PARM is the variable that indicates the type of data we are selecting.
DECLARE CURSOR Select Name, Addr, SSN, Age, CASE :WS-PARM WHEN 'CH' THEN NAME WHEN 'AD' THEN DIGITS(SSN) WHEN 'SC' THEN DIGITS(AGE) END, CASE :WS-PARM WHEN 'CH' THEN ADDR WHEN 'AD' THEN NAME WHEN 'SC' THEN DIGITS(SSN) END FROM TABLE WHERE REC_TYPE = :WS-PARM ORDER BY 5,6The Case statements create two additional columns which are the fifth and sixth columns. Based on the type of data, different values will be in those columns. The important thing to remember is that these columns must contain the same type of data, but not necessarily the same length.
The DIGITS function was used to convert the numeric columns to alphabetic. It doesn't matter if the NAME, ADDR, SSN, or AGE is chosen in the column because it is always returning a character value.
When you code the FETCH for the cursor and are setting up HOST VARIABLES for the fifth and sixth columns, you will always want the host variable to be big enough to hold the longest value that can be in the column.
For the fifth column in this case, that would be the NAME. Your host variable should be 25 characters in length.
For the sixth column, the longest field would be the ADDR field. You would want that host variable to be 30 characters in length.
You now have the ability to sort the same data in multiple ways without multiple cursors or dynamic SQL.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.