DB2 & IMS

SELECT      ACCT_NUM, ACCT_RATING_CDE,

(SELECT MAX(DOLLAR_AMT)

FROM ACCT_HIST B

WHERE B.ACCT_ID = A.ACCT_ID)

FROM ACCOUNT A

WHERE       ACCT_ID = 543670001

The ability to put a SELECT in a SELECT list can help make report-writing queries more efficient. The following example shows how to use a scalar fullselect in the WHERE clause. Here, we’re comparing ACCT_VALUE between the results of two other SELECTs, which are obtaining values from other tables. A statement such as the one shown in this example would have taken several SQL calls in the past with additional program code:

SELECT ACCT_NUM

FROM ACCOUNT A

WHERE ACCT_VALUE BETWEEN

6 Pages