Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> Oracle Syntax Migration (GaussDB A)> Pseudo Columns
View PDF

Pseudo Columns

This section contains the migration syntax of Oracle Pseudo Columns. The migration syntax decides how the keywords/features are migrated.

A pseudo column is similar to a table column, but is not actually stored in the table. User can select values from pseudo columns, but cannot insert, update, or delete values in the pseudo columns.

ROWID

The ROWID pseudo column returns the address of a specific row.

Figure 1 Input - ROWID
Figure 2 Output - ROWID

ROWNUM

For each row of data returned for a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Figure 3 Input - ROWNUM
Figure 4 Output - ROWNUM

Input-ROWNUM with UPDATE

When executing UPDATE, if ROWNUM with some value (integer) is used, the system will UPDATE records using the operator near ROWNUM accordingly.

UPDATE SCMS_MSGPOOL_LST
             SET MSG_STD = '11'
           WHERE UNISEQNO = IN_OUNISEQNO
             AND MSG_TYP1 IN ('MT103', 'MT199')
             AND ROWNUM = 1;

Output

 UPDATE SCMS_MSGPOOL_LST
   SET MSG_STD = '11'
 WHERE (xc_node_id,ctid) in (select xc_node_id, ctid 
        from SCMS_MSGPOOL_LST 
        where UNISEQNO = IN_OUNISEQNO
        AND MSG_TYP1 IN ('MT103', 'MT199')
        LIMIT 1)

Input-ROWNUM with DELETE

When exectuing DELETED, if ROWNUM with some value (integer) is used, system will DELETE records using the operator near ROWNUM accordingly.

delete from test1 
where c1='abc' and rownum = 1;

Output

delete from test1 where (xc_node_id,ctid) in (select xc_node_id, ctid from test1 where c1='abc' limit 1);

Input - UPDATE with ROWNUM

Scripts of UPDATE and DELETE that are migrated through ROWNUM contains LIMIT, which is not supported by Gauss.

UPDATE SCMS_MSGPOOL_LST
 SET MSG_STD = '11'
 WHERE UNISEQNO = IN_OUNISEQNO
 AND MSG_TYP1 IN ('MT103', 'MT199')
 AND ROWNUM = 1;

Output

UPDATE SCMS_MSGPOOL_LST
   SET MSG_STD = '11'
 WHERE (xc_node_id, ctid) = ( SELECT xc_node_id, ctid
           FROM SCMS_MSGPOOL_LST
          WHERE UNISEQNO = IN_OUNISEQNO
         AND MSG_TYP1 IN ('MT103', 'MT199') 
          LIMIT 1
       );

Input - DELETE with ROWNUM

DELETE FROM SPMS_APP_PUBLISH
 WHERE NOVA_NO = IN_NOVA_NO
   AND DELIVERY_TYPE = '1'
   AND PUBLISH_DATE = IN_PUBLISH_DATE
   AND ROWNUM = 1;

Output

DELETE FROM SPMS_APP_PUBLISH
 WHERE (xc_node_id, ctid) IN (SELECT xc_node_id, ctid
           FROM SPMS_APP_PUBLISH
          WHERE NOVA_NO = IN_NOVA_NO
         AND DELIVERY_TYPE = '1'
         AND PUBLISH_DATE = IN_PUBLISH_DATE 
          LIMIT 1
        );