Values and Value Expressions › Expansion of rowid-pseudo-column

Expansion of rowid-pseudo-column

The expanded parameters of rowid-pseudo-column request the ROWID values to be determined when the SQL statement in which they appear is executed.

Syntax

Expansion of rowid-pseudo-column

►►─┬──────────────────────────────────────────┬───┬──── ROWID ────────────────►◄
   ├──┬──────────────┬┬─ table-identifier. ───┤   │
   │  └ schema-name. ┘└── view-identifier. ───┘   │
   └──────────────── alias. ──────────────────────┘

Parameters

schema-name

Specifies the schema with which the table or view identified by table-identifier or view-identifier is associated.

Note: For more information about using a schema name to qualify a table or view identifier, see Identifying Entities in Schemas.

table-identifier

Identifies a base table defined in the dictionary.

view-identifier

Identifies a view defined in the dictionary.

alias

Specifies the alias associated with the table or view to which the ROWID pseudo-column refers. The alias must be defined in the FROM parameter of the subquery, query specification, or SELECT statement that includes the ROWID.

Usage

Because the pseudo-column ROWID obviously becomes easily ambiguous when multiple tables or views are involved in an SQL statement, qualification is required in most, but the simplest statements.

Note: ROWID can generally also be used for tables associated with native VSAM files. However for KSDS native VSAM files ROWID cannot be used to directly access a KSDS record.

Examples

Using ROWID in a Simple SELECT Statement

SELECT ROWID, OFFICE_CODE_0450, OFFICE_CITY_0450
  FROM EMPSCHM.OFFICE;
*+
*+     ROWID     OFFICE_CODE_0450  OFFICE_CITY_0450
*+     --------  ----------------  ----------------
*+   X'01259701'  002               BOSTON
*+   X'0125A001'  001               SPRINGFIELD
*+   X'0125A301'  005               GLASSTER
*+   X'0125A601'  012               CAMBRIDGE
*+   X'0125A901'  008               WESTON

The values of ROWID are displayed as hexadecimal values, which in this case are also the values of the DBKEY for the OFFICE record in the non-SQL-defined schema EMPSCHM VERSION 100 of the demo employee database.

Using ROWID in the WHERE clause of a Searched UPDATE Statement

UPDATE EMPSCHM.EMPLOYEE SET EMP_CITY = 'BRUSSELS'
  WHERE ROWID = X'0124FF01';

The column EMP_CITY of the EMPLOYEE record in the non-SQL schema EMPSCHM VERSION 100 is updated for the record whose DBKEY is X'0124FF01'.

Using ROWID in a JOIN of a Base Table and a View

Both examples use DEFJE01.EMPLOYEEV which is defined as follows:

CREATE VIEW DEFJE01.EMPLOYEEV
    AS SELECT * FROM EMPSCHM.EMPLOYEE;

In the first example DEFJE01.EMPOFFV is defined as follows:

CREATE VIEW DEFJE01.EMPOFFV
 AS SELECT EV.*, O.*
  FROM EMPSCHM.OFFICE O, DEFJE01.EMPLOYEEV EV
 WHERE "OFFICE-EMPLOYEE";

The returned ROWID for the view is the ROWID of the EMPSCHM.OFFICE base table:

SELECT EOV.ROWID, D.ROWID, D.*, EMP_ID, OFFICE_CODE_0450
  FROM DEFJE01.EMPOFFV EOV, EMPSCHM.DEPARTMENT D
 WHERE "DEPT-EMPLOYEE" AND EMP_ID < 5;
*+
*+     ROWID         ROWID     DEPT_ID_0410
*+     --------      --------  ------------
*+  X'0125A001'   X'0125BD01'           100
*+  X'0125A001'   X'0125BC01'          3100
*+  X'0125A001'   X'0125AB01'          3200
*+
*+ DEPT_NAME_0410            DEPT_HEAD_ID_0410  EMP_ID
*+ ----------                -----------------  ------
*+ EXECUTIVE ADMINISTRATION                 30       1
*+ INTERNAL SOFTWARE                         3       3
*+ COMPUTER OPERATIONS                       4       4
*+
*+ OFFICE_CODE_0450
*+ ----------------
*+ 001
*+ 001
*+ 001

In the second example, DEFJE01.EMPOFFV is defined as follows:

CREATE VIEW DEFJE01.EMPOFFV
 AS SELECT EV.*, O.*
  FROM DEFJE01.EMPLOYEEV EV, EMPSCHM.OFFICE O
 WHERE "OFFICE-EMPLOYEE";

The returned ROWID for the view is the ROWID of the EMPSCHM.EMPLOYEE base table, which is the first base table in the view EMPLOYEEV.

SELECT EOV.ROWID, D.ROWID, D.*, EMP_ID, OFFICE_CODE_0450
  FROM DEFJE01.EMPOFFV EOV, EMPSCHM.DEPARTMENT D
 WHERE "DEPT-EMPLOYEE" AND EMP_ID < 5;
*+
*+  ROWID         ROWID     DEPT_ID_0410
*+  --------      --------  ------------
*+  X'01252801'   X'0125BD01'           100
*+  X'01253B01'   X'0125BC01'          3100
*+  X'01255301'   X'0125AB01'          3200
*+
*+  DEPT_NAME_0410           DEPT_HEAD_ID_0410  EMP_ID
*+  --------------           -----------------  ------
*+ EXECUTIVE ADMINISTRATION                30       1
*+ INTERNAL SOFTWARE                        3       3
*+ COMPUTER OPERATIONS                      4       4
*+
*+ OFFICE_CODE_0450
*+ ----------------
*+ 001
*+ 001
*+ 001

Searched Update of Records Without Primary Key

This example updates all the COVERAGE records of the employee with EMP_ID=23:

UPDATE EMPSCHM.COVERAGE C
  SET SELECTION_YEAR_0400 = 20
 WHERE C.ROWID IN (
           SELECT CI.ROWID
             FROM EMPSCHM.EMPLOYEE E, EMPSCHM.COVERAGE CI
            WHERE "EMP-COVERAGE"
              AND EMP_ID = 23);
*+ Status = 0        SQLSTATE = 00000
*+ 2 rows processed

Searched Delete of Records Without Primary Key

This example deletes all the COVERAGE records of the employee with EMP_ID=23:

DELETE FROM EMPSCHM.COVERAGE C
 WHERE C.ROWID IN (
       SELECT CI.ROWID
         FROM EMPSCHM.EMPLOYEE E, EMPSCHM.COVERAGE CI
        WHERE "EMP-COVERAGE"
          AND EMP_ID = 23);
*+ Status = 0        SQLSTATE = 00000
*+ 2 rows processed