Retrieving data by using the SELECT statement

The simplest way to retrieve data is to use the SQL SELECT statement to specify a result segment. You can specify the fields and rows that you want to retrieve.

You do not need to know the field names to select IMS data. Use an asterisk (*) in the SELECT clause to indicate that you want to retrieve all fields of each selected row of the named segment. To view the values of these fields, you must specify the field name.

The fields in a SELECT * statement are ordered first by their field position within the IMS catalog and then by their length.

Example: SELECT *: The following statement retrieves all fields for the PATIENT segment:

SELECT *
  FROM PCB01.HOSPITAL;

The result segment looks similar to the following output:

+------------+-----------------+
|HOSPCODE    |HOSPNAME         |
+------------+-----------------+
|R1210010000A|ALEXANDRIA       |
|R1210020000A|SANTA TERESA     |
|R1210030000A|SANTA CLARA      |
|R1210040000A|NEW ENGLAND      |

Because the example does not specify a WHERE clause, the statement retrieves data from all rows.

SELECT * is not recommended when fetching into a static host structure because of host variable compatibility and performance reasons.

Note:

Suppose that you add a field to the segment to which SELECT * refers. If you have not defined a receiving host variable for that field, an error occurs.
If you list the field names in an SELECT statement instead of using an asterisk, you can avoid the problem that sometimes occurs with SELECT *. You can also see the relationship between the receiving host variables and the fields in the result segment.

Selecting some fields: SELECT field-name:

Select the field or fields you want to retrieve by naming each field. All fields appear in the order you specify, not in their order in the segment.

Example: SELECT field-name: The following statement retrieves the ward names and patient names from the WARD and PATIENT tables, respectively:

SELECT HOSPNAME FROM PCB01.HOSPITAL

The result segment looks similar to the following output:

+-----------------+
|HOSPNAME         |
+-----------------+
|ALEXANDRIA       |
|SANTA TERESA     |
|SANTA CLARA      |
|NEW ENGLAND      |

With a single SELECT statement, you can select data from one field or as many as 750 fields.

Selecting rows using search conditions: WHERE:

Use a WHERE clause to select the rows that meet certain conditions. A WHERE clause specifies a search condition. A search condition consists of one or more predicates. A predicate specifies a test that you want IMS to apply to each segment row.

IMS evaluates a predicate for each row as true, false, or unknown. Results are unknown only if an operand is null.

The following segment lists the type of comparison, the comparison operators, and an example of each type of comparison that you can use in a predicate in a WHERE clause.

Table 1. Comparison operators used in conditions
Type of comparison	Comparison operator	Example
Equal to	=	HOSPCODE = 'R1210010000A'
Not equal to	<>	HOSPCODE <> 'R1210020000A'
Less than	<	SALARY < 30000
Less than or equal to	<=	AGE <= 25
Not less than	>=	AGE >= 21
Greater than	>	WARDNO > '0001'
Greater than or equal to	>=	WARDNO >= '0003'
Not greater than	<=	PATNUM <= '0010'
At least one of two conditions	OR	HOSPCODE >= 'R1210010000A' OR HOSPCODE < 'R1210050000A'
Both of two conditions	AND	HOSPCODE = 'R1210050000A' AND HOSPNAME = 'SANTA TERESA'

Both of these forms of the predicate create an expression for which one value is equal to another value or both values are equal to null.