WHERE clause usage

In a SQL SELECT, UPDATE, and DELETE statement, the WHERE clause can be used to select data conditionally.

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. If you do not table-qualify a column, there can be ambiguity if that column exists in more than one table that was joined in the FROM clause.

The IMS JDBC drivers convert the WHERE clause in an 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, it is legal to compare one column to another column if one column is the foreign key and the other column is the primary key it is referencing. For 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
For example, the following WHERE clause will fail because it is trying to compare two columns:
```
WHERE PAYMENTS.PATNUM=PAYMENTS.AMOUNT
```
The following example is valid because the WHERE clause is comparing a column to a value:
```
WHERE PAYMENTS.PATNUM='A415'
```
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 will fail 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>?
```