 | |||
|
|
||
| |||
| |||
| |
|
Home > AS/400 Tips > WebSphere Strategies for iSeries professionals > Finding the Value in SQL and WebSphere |
| |
|
|
|
| iSeries 400 Tips: |
|
|
|
|
|
|
|
|
|||||
|
WEBSPHERE STRATEGIES FOR ISERIES PROFESSIONALS Finding the Value in SQL and WebSpherePaul Holm and Colin Slade 05.20.2004 Rating: -4.85- (out of 5)
|
[IMAGE] | |||||
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:
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 nu
Digg This!
 StumbleUpon
 Del.icio.us
|
 |
 |
| |
RELATED GLOSSARY TERMS
|
||||
|
|
| |
RELATED RESOURCES
|
ll.
Solution:
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:
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.
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.
Conclusion:
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 pholm@planetjavainc.com.
Colin Slade is a Web developer at PlanetJ Corp.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk. |
|
|||
