SQL and JDBC are the standard language for relational database management systems. SQL statements are used to perform tasks such as selects, updates, inserts, and deletes from a database. Most Web applications built for WebSphere make extensive use of SQL. A frequent problem encountered for Web application development is when attempting to allow optional selection criteria.
Many applications allow the user to select records based on selection criteria. Let's say you need to create an application that allowed users to search for used truck trailers. We wanted to allow them to specify a dynamic range of selection criteria. For example: Select based on trailer type, cost, state, age or any combination of these.
If they don't specify criteria, the search should ignore it. That is the big trick, how to ignore it if they don't specify a value.
This article will detail how this type of search can be coded and also how to ignore values in the search if the user does not specify them.
SQL does not like null values and will normally not take one unless specifically stated in the SQL expression. So for the example with used trailers, you want to get the user to select based on trailer type, cost, state, age or any combination of these. To do that you would use a normal select statement with the ? To get input from the user, an example might be this:
(Select * FROM trailertable WHERE trailor_type = ? AND cost = ?)
The problem with that statement is it requires both conditions to evaluate to true. What happens if the user does not specify a cost? The answer is they would get no records returned, which is not what we want. If you use the AND statement like above you must enter both values to get a valid selection from the table. This significantly narrows your search so that none of your options can be blank or null.
The solution to this problem is the VALUE function provided in SQL. The VALUE function returns the first argument in a list that is not null. The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The result can be null only if all the arguments can be null, and the result is null only if all the arguments are null. The selected argument is converted, if necessary, to the attributes of the result. Here is an example:
SELECT * FROM trailortable WHERE trailor_type = VALUE (CAST (? AS DECIMAL (3, 0)), trailor_type) AND cost = VALUE (CAST (? AS DECIMAL (3, 2)), cost)
This example selects all of the rows from the trailer table where trailer type and cost are taken from the user the input. The SQL statement is prepared using standard Java prepared statements. The input if specified is cast into a decimal number and returned as the comparison operand. If the user did not specify a value, then the current value of the field itself is used which effectively would result in a true condition in all cases. The VALUE function results in these comparisons if no values are entered by the user.
(select * from trailertable where trailer_type = trailer_type and cost = cost)
As you can see from this SQL, it would result in all records being return, which is the desired result. If the user specifies only one of the two criteria, then only the one specified is used as part of the selection criteria.
Here is a Web site example: www.wabashusedtrailers.com.
When creating Web applications it is important to give the user the ability to specify selection criteria but not necessarily require it. With the SQL VALUE function you are able to accommodate such a requirement, resulting in a effective and flexible WebSphere application. Put this tip to use and unleash the VALUE in your application.
About the authors: Paul Holm is a former IBM-Rochester WebSphere, Java, and DB2 developer/consultant. He specializes in helping iSeries RPG shops develop Web-based applications. Paul is currently a lead architect for the "WOW" product, which is Query/400 and DFU for WebSphere (aka WebSphere on steriods). Paul can be reached at email@example.com.
Colin Slade is a Web developer at PlanetJ Corp.