WHERE clause in Queries

Syntax

<Query>.WHERE (<Condition>)

Description

The records that are retrieved from the database can be filtered with a ‘where’ clause.

With the WHERE clause for table queries and cursor queries, an extra condition can be sent to the database server to filter the table. The condition is specified between parentheses and may contain operators and functions. Function calls can be only be used on the right side of an operator.

In a WHERE clause the left element of the condition must always be the database field.
Using a database field of an other query as left element, may possibly cause invalid results.

When a table/cursor query column variable is used on the left-side of an expression it represent the column name, when its used at the right-side of an expression it represents the column variable value during the start of the selection.
E.g. the code tq_cust.where (tq_cust.housenr = tq_cust.housenr) will be translated to the SQL code WHERE ([HOUSENR]=?);


In previous AMT versions, fieldnames were supported on the right-side of an expression. To revert to this behaviour, the Generation Set switch "SQLWHEREEXPR" can be set.

Operators that are supported by the WHERE are =, <>, <, >, <=, >=, NOT, AND, OR, IN and LIKE.


Inside a WHERE clause concatenation will be translated in different ways depending on the operators used. To prevent numeric to alpha conversion issues, the & (Ampersand) operator when used in combination with table fields will be translated to a Concat function for MsSql databases and to the \|\| (double vertical bar) operator for Oracle. The + (plus-sign) operator when used in combination with table fields will be kept as + (plus-sign) in the database query. Other variables (excluding table fields) or literals in the where clause which use the + or & operators for concatenation, will be combined in AMT and translated to a single database parameter. As a result, the AMT rules regarding type conversions will apply to these concatenations.

Inside a WHERE clause concatenation will be translated in different ways depending on the operators used.

To prevent numeric to alpha conversion issues, the & (Ampersand) operator when used in combination with table fields will be translated to a Concat function for MsSql databases and to the || (double vertical bar) operator for Oracle.
The + (plus-sign) operator when used in combination with table fields will be kept as + (plus-sign) in the database query.

Other variables (excluding table fields) or literals in the where clause which use the + or & operators for concatenation, will be combined in AMT and translated to a single database parameter. As a result, the AMT rules regarding type conversions will apply to these concatenations.

The SQL functions that are supported by the WHERE are:

SQL function	Description
LEFT*	Gets a number of characters from the left side of a table field value.
	Use:	Operation:
		LEFT (<Query handler>.<Fieldname>, <Result length>)
		<Query handler>	Name of the query handler to be used for fetching the table field.
		<Fieldname>	Reference to the table field from where the characters must be copied.
		<Result length>	Number of characters to be returned.
	Example:	qt_customer.WHERE (LEFT(qt_customer.CITY, 4) = ’Amst’)
LOWER*	Returns the field value or variable as lowercase.
	Use:	Operation:
		LOWER (<Query handler>.<Fieldname>) or LOWER (<variable>)
		<Query handler>	Name of the query handler to be used for fetching the table field.
		<Fieldname>	Reference to the table field from where the characters must be copied.
		<Variable>	A local or global defined variable.
	Examples:	qt_customer.WHERE (LOWER(qt_customer.CITY) = ’amsterdam’) qt_customer.WHERE (LOWER(qt_customer.CITY) = LOWER(cstcty))
RIGHT*	Gets a number of characters from the right side of a table field value.
	Use:	Operation:
		RIGHT (<Query handler>.<Fieldname>, <Result length>)
		<Query handler>	Name of the query handler to be used for fetching the table field.
		<Fieldname>	Reference to the table field from where the characters must be copied.
		<Result length>	Number of characters to be returned.
	Example:	qt_customer.WHERE (RIGHT(qt_customer.CITY, 4) = ’rdam’)
SUBSTRING*	Returns a substring from the table field value.
	Use:	Operation:
		SUBSTRING (<Query handler>.<Fieldname>, <Start position>, <Length>)
		<Query handler>	Name of the query handler to be used for fetching the table field.
		<Fieldname>	Reference to the table field from where the characters must be copied.
		<Start position>	Start position from where in the table field value the substring must be copied.
		<Length>	Number of characters that must be copied.

	Example:	qt_customer.WHERE (SUBSTRING(qt_customer.CITY, 3, 5) = ’sterd’)
UPPER*	Returns the field value or variable as uppercase.
	Use:	Operation:
		UPPER (<Query handler>.<Fieldname>) or UPPER (<Variable>)
		<Query handler>	Name of the query handler to be used for fetching the table field.
		<Fieldname>	Reference to the table field from where the characters must be copied.
		<Variable>	A local or global defined variable.
	Example:	qt_customer.WHERE (UPPER(qt_customer.CITY) = ’AMSTERDAM’) qt_customer.WHERE (UPPER(qt_customer.CITY) = UPPER(’cstcty’))

*) When using a query with the functions LEFT, LOWER, RIGHT, SUBSTRING and/or UPPER with an MS-SQL database manager, the performance will be slower than when using similar queries with either the = or LIKE operator.

The advantage of using the WHERE clause is that this can speed up the performance of your database transactions considerably. This advantage is gained because the database server only returns the filtered records to the Application server. So, this way it is not necessary to return all the records first and then check each record at runtime on being needed or not. In general, the larger the tables and the slower your database connection, the bigger the gain in performance. Besides, if there is a big difference between the number of fields that are expected to be used and the total number of fields in the source table it is also very much recommended to use the WHERE clause.

Although there are more options for filtering available for free join queries, the most common are also available when using the another query type with the WHERE clause. By nesting the conditions there will be hardly any limitation for specifying very powerful filters. Besides, you will keep the syntax check option (in difference with using free join queries).

Please note that the WHERE clause is evaluated only once by the database server. "Only once" means that the expression of the clause is evaluated by the database-engine before the query is executed, and then the retrieved database-rows are returned. So, if your routine contains the WHERE condition that is based on a variable (as in the 1st example, which uses the variable 'country') the resultset won't be influenced in any way if this variable is changed afterwards. Also the EQUAL option as it is used in the 2nd example has not any effect on the resultset that is returned for the WHERE condition because that condition will always be executed at the start of the table query execution.

You may test a database-field on the value "NULL". This is particularly useful in a situation where a recent reorganization has added a new field to a table, and not all records have been updated with spaces or zeros. The default value in these fields then still is "NULL".

Examples

(A)
tq_cust.index (i_customername)
tq_cust.start ('')
tq_cust.where (tq_cust.country = 'Netherlands')
loop tq_cust
    sa-100 := '|' + tq_cust.code
    replace (sa-100, 7, '|')
    sa-100 := sa-100 + tq_cust.code
    replace (sa-100, 14, tq_cust.name)
    fillbox (si-currform + '.customer', sa-100)
endloop
if not resok
    sa-100 := '| |no customers in the Netherlands'
    fillbox (si-currform + '.customer', sa-100)
endif

(B)
tq_cust.index (idx_custno)
tq_cust.start (0)
tq_cust.where (tq_cust.mailing = 'Y')
loop tq_cust
    send-mail(tq_cust.emailaddress)
endloop

(C)
tq_cust.index (idx_custno)
tq_cust.start (0)
tq_cust.where (tq_cust.country = null)
loop tq_cust
    tq_cust.country := ''
    tq_cust.update (country)
endloop