Previous Topic Next topic Print topic


Subselect Clause

Create subselect clause by embedding a complete DB-OBTAIN call within a WHERE clause.
Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Target:

SQL

Syntax:

Embed a DB-OBTAIN call by enclosing it in parentheses. The syntax for the embedded DB-OBTAIN is the same as that for a standard DB-OBTAIN.

General Rules:

  1. Create subselect code in a DB-DECLARE, DB-OBTAIN, DB-PROCESS, or DB-STORE call.
  2. All standard SQL requirements for a subselect clause apply to the embedded DB-OBTAIN.
  3. The number and type of columns coded must match the DB-OBTAIN column sequence; both calls must specify columns from different tables.
  4. When you code a DB-STORE call, make sure that you:
    • Make the number of selected columns for DB-STORE match the number of columns in the result table for the subselect.
    • Do not code the same copylibname-REC in both the DB-STORE and subselect calls.
    • Do not code DB STORE FROM dataname.
    • Do not code alternate values for any columns in DB-STORE.
    • Do not enclose the subselect statement in parentheses, as you do for other DB calls.

Example:

DB-OBTAIN REC A.D2TAB-REC
... PM_PART_NO PM_UNIT_BASE_PRICE
... WHERE EXISTS
... (DB-OBTAIN REC D2INVEN-REC
... WHERE IN_PART_NO = A.PM_PART_NO)
DB-DECLARE D2MAST-CURSOR D2TAB-REC
... PM_PART_NO PM_UNIT_BASE_PRICE PM_COLOR
... WHERE PM_PART_NO IN
... (DB-OBTAIN
... REC D2INVEN-REC IN_PART_NO
... WHERE IN_QTY_ONHAND > 100)
... AND PM_UNIT_BASE_PRICE BETWEEN 50 and 100
DB-PROCESS REC D2TAB-REC
... PM_PART_NO PM_UNIT_BASE_PRICE PM_COLOR
... WHERE PM_UNITS <
... (DB-OBTAIN REC D2TAB-REC
... AVG(PM_UNITS))
DB-STORE REC D2INVEN-REC
... IN_PART_NO IN_QTY_ONHAND
... DB-OBTAIN REC D2TAB-REC
... PM_PART_NO PM_PART_SHORT_DESC
... WHERE PM_UNITS > '99'
Previous Topic Next topic Print topic