Deal of the Day

Friday 8 July 2011

SQL where Clause (Row Value Expressions)


Hi Guys,


Again thanks to Issak (my linkedin Friend) who made me re-call the new feature which was introduce in V5R4 for SQL Where clause
Quoting his words.
SQL WHERE clause usually checks if a condition is true, false or unknown. In the past, each condition was evaluated for a single value. Starting with V5R4 groups or "rows" of values can be evaluated at once. These groups of values are called "row value expressions." 


Below is a simple example with constants: 

SELECT * FROM DEPTBL WHERE (DEPT, BRANCH)=('Shipping', '0012M')

The number of columns must be equal and of compatible data types. 

Statement provides the same result as 

SELECT * FROM DEPTBL WHERE DEPT='Shipping' AND BRANCH='0012M' 


Please note the parentheses usage in first example. 

The same technique applies to IN (SELECT…) predicate. However a constant list is not allowed when using a row value expression in this case. 

This saves some coding, but could look confusing in the beginning. It is just the matter of preferences.



Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning


No comments:

Post a Comment