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.
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:
- Create subselect code in a DB-DECLARE, DB-OBTAIN, DB-PROCESS, or DB-STORE call.
- All standard SQL requirements for a subselect clause apply to the embedded DB-OBTAIN.
- The number and type of columns coded must match the DB-OBTAIN column sequence; both calls must specify columns from different tables.
- 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'
