DB-DECLARE

Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

All rows and columns in a table
All columns, from specific rows, in a table
Specific columns in a table
Specific columns, from specific rows, in a table

Note the following when coding DB-DECLARE:

Declare a cursor before coding a DB-OPEN or DB-FETCH call.
Because a declared cursor name is referenced by all subsequent calls for that cursor, code UPDATE to specify which columns can be modified; otherwise columns cannot be modified in subsequent cursor processing.
When specifying columns for sorting, identify them either by name or position in the selection list; do not mix references.

Target:

SQL

Syntax: for Format 1

Unqualified, select all columns.

DB-DECLARE cursorname copylibname-REC
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 2

Qualified, select all columns.

DB-DECLARE cursorname copylibname-REC
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... WHERE column operator [[:]altvalue]|column
... [AND|OR column operator [[:]altvalue]|column]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 3

Select specific columns.

DB-DECLARE cursorname copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... column1 [(altvalue)] [... columnN [(altvalue)]]
... [WHERE column operator [[:]altvalue]|column
... [AND|OR correlname.]column operator 
... [[:]altvalue]|column]
        .
        .
        .
... [AND|OR correlname.]column operator
... [[:]altvalue]|column]]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 4

Implicit Join selecting columns from two or more tables.

DB-DECLARE cursorname correlname.copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [(altvalue)] [... columnN [(altvalue)]]]
... [correlname.copylibname-REC
... [column1 [(altvalue)] [... columnN [(altvalue)]]]
        .
        .
        .
... [WHERE correlname.column oper
... [:]altvalue|correlname.column
... [AND|OR correlname.column oper
... [:]altval|correlname.column]
        .
        .
        .
... [AND|OR correlname.column oper
... [:]altvalue|correlname.col]]
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 5

Specify a UNION.

DB-DECLARE cursorname copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [(altvalue)] [... columnN [(altvalue)]]]
... [WHERE column operator [[:]altvalue]|column
... [AND|OR column operator [[:]altvalue]|column]
        .
        .
        .
... [AND|OR column operator [[:]altvalue|]column]]
... UNION [ALL]
DB-OBTAIN REC copylibname-REC
        .
        .
        .
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 6

Specify a Join using a join-operator.

DB-DECLARE cursorname correlname1.copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [... columnN]]|[NONE]
           .
           .
           .
... [[INNER JOIN|RIGHT OUTER JOIN|LEFT OUTER JOIN|FULL OUTER JOIN] 
... ON joincondition REC]
... correlnameN.copylibname-REC
... [column1 [... columnN]]|[NONE]
           .
           .
           .
... [WHERE correlname.column1 oper
... [:]value|correlname.column2
... [AND|OR correlname.column3 oper
... [:]value|