Filter Examples

Example 1

The following examples show how to select all records that contain a data item that meets the WHERE criteria. The data items can be selected only from the specified data set.

SELECT ALL FROM BANK WHERE REGION-ID = 10;

SELECT * FROM BRANCH WHERE BRANCH-NAME = "Pittsburgh"

Example 2

The following two examples show how to select one or more data items from a data set.

This example shows one data item to be selected from the data set FINANCIAL-STATUS. Note that FS-2 is a Boolean.

SELECT FS-NAME FROM FINANCIAL-STATUS WHERE FS-2;

This example selects two data items (BRANCH-ID and BRANCH-AD1) for the data set named BRANCH.

SELECT BRANCH-ID BRANCH-AD1 FROM BRANCH

WHERE BRANCH-ID > 1

AND BRANCH-NAME = "SOUTH MANHATTAN"

AND BRANCH-AD1 NEQ " ";

Example 3

The following example shows how to select one or more data items from a variable-format data set.

SELECT % variable-format dataset

1: AC-TYPE AC-NUMBER CUST-ID AC-BALANCE

AC-STAT1 BRANCH-ID AC-HLD-AMT

2: AC-TYPE AC-NUMBER AC-STAT2

3: AC-NUMBER, AC-STAT3, AC-TYPE

5: *

0: AC-NUMBER AC-DT-CLSE

FROM ACCOUNT WHERE BANK-ID = 1;

The following examples build on the previous example, using the ELSE: clause (assuming ACCOUNT has a record type 4):

filter NoType4

% -------

select 1: AC-TYPE AC-NUMBER AC-BALANCE AC-VF1-GROUP

BRANCH-ID AC-HLD-AMT

2: AC-TYPE AC-NUMBER AC-VF2-GROUP

3: AC-NUMBER AC-VF3-GROUP AC-TYPE

0: AC-NUMBER AC-DT-CLSE

% no type 4 in enumeration; no type 4 updates

else: none

from ACCOUNT where BANK-ID > 5000;

filter WithType4

% ---------

select 1: AC-TYPE AC-NUMBER AC-BALANCE AC-VF1-GROUP

BRANCH-ID AC-HLD-AMT

2: AC-TYPE AC-NUMBER AC-VF2-GROUP

3: AC-NUMBER AC-VF3-GROUP AC-TYPE

0: AC-NUMBER AC-DT-CLSE

% type 4 is in enumeration; type 4 updates

% must satisfy the WHERE clause

else: *

from ACCOUNT where BANK-ID > 5000;

filter NoType4PerDefault

% -----------------

default none

select 1: AC-TYPE AC-NUMBER AC-BALANCE AC-VF1-GROUP

BRANCH-ID AC-HLD-AMT

2: AC-TYPE AC-NUMBER AC-VF2-GROUP

3: AC-NUMBER AC-VF3-GROUP AC-TYPE

0: AC-NUMBER AC-DT-CLSE

% no type 4 in enumeration; no type 4 updates

from ACCOUNT where BANK-ID > 5000;

filter Type4PerDefault

% ---------------

default all

select 1: AC-TYPE AC-NUMBER AC-BALANCE AC-VF1-GROUP

BRANCH-ID AC-HLD-AMT

2: AC-TYPE AC-NUMBER AC-VF2-GROUP

3: AC-NUMBER AC-VF3-GROUP AC-TYPE

0: AC-NUMBER AC-DT-CLSE

% type 4 is in enumeration; type 4 updates

% must satisfy the WHERE clause

from ACCOUNT where BANK-ID > 5000;