WHERE clause usage

Use the WHERE clause to select data conditionally in SQL SELECT, UPDATE, or DELETE statements.

When using the WHERE clause with the IMS Universal JDBC driver, use columns that are in any table listed in the FROM clause.

Recommendation: Qualify columns with table names to avoid the ambiguity that can arise if a column exists in multiple tables that are joined in the FROM clause.

The IMS JDBC drivers convert the WHERE clause in a SQL query to a segment search argument (SSA) list when querying a database. SSA rules restrict the type of conditions you can specify in the WHERE clause. The following restrictions apply:

In general, compare columns to values, not other columns. With the introduction of foreign keys, you can compare one column to another column if one column is the foreign key and the other column is the primary key it is referencing. Example:
```
WHERE HOSPITAL_HOSPCODE = HOSPITAL.HOSPCODE
```
You can use the following operators between column names and values in the individual qualification statements:

=

Equals

!=

Not equal

>

Greater than

>=

Greater than or equals

<

Less than

<=

Less than or equals

The WHERE clause in the following example is valid because it compares a column to a value:
```
WHERE PAYMENTS.PATNUM='A415'
```
In contrast, the following WHERE clause is invalid and fails because it compares two columns:
```
WHERE PAYMENTS.PATNUM=PAYMENTS.AMOUNT
```
Do not use parentheses. Qualification statements are evaluated from left to right. The order of evaluation for operators is the IMS evaluation order for segment search arguments.
List all qualification statements for a table adjacently. For example, in the following valid WHERE clause, the qualified columns from the same PATIENT table are listed adjacently:
```
WHERE PATIENT.PATNAME='BOB' OR PATIENT.PATNUM='A342' AND WARD.WARDNO='52' 
```
The following invalid WHERE clause fails because the columns from the HOSPITAL table are separated by the columns from the WARD table:
```
WHERE HOSPITAL.HOSPNAME='Santa Teresa' AND WARD.WARDNO='52'  
OR WARD.WARDNAME='CARD' AND HOSPITAL.HOSPCODE='90' 
```
The OR operator can be used only between qualification statements that contain columns from the same table. You cannot use the OR operator across tables. To combine qualification statements for different tables, use an AND operator. For example, the following invalid WHERE clause will fail:
```
WHERE WARD.WARDNO='03' OR PATIENT.PATNUM='A415'
```
However, the following WHERE clause is valid because the OR operator is between two qualification statements for the same table:
```
WHERE PATIENT.PATNUM='A409' OR PATIENT.PATNAME='Sandy'
```
When using prepared statements, you can use the question mark (?) character, which is later filled in with a value. For example, the following WHERE clause is valid:
```
WHERE PAYMENTS.AMOUNT>?
```
When using a column that is a virtual foreign key in the qualification statements, you must follow these rules when access to the database is random:
- Specify a virtual foreign key that is only from one of the first child segments of the root segment (level 2). You cannot specify virtual foreign keys that are at lower levels in the hierarchy.
- Use the OR operator to combine multiple qualification statements that use the virtual foreign key.
- Specify qualifications statements that use only the virtual foreign key. For example, you cannot use the OR or the AND operator to specify another column from the table or another table.
For example, the following WHERE clause is valid:
```
WHERE HOSPITAL_HOSPCODE='ARS100100D' OR HOSPITAL_HOSPCODE='ARS100100D'
```

(Optional) ssaOptimization- This property indicates whether to optimize the SSA List for SQL queries that include columns that are a subset of a primary key or a secondary index in the WHERE clause. The SSA List would be modified to a ranged search on the key provided that the column/columns cover the beginning byte/bytes of the key offset range. The default value is false.