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 by the query, the ROWNUM pseudo column returns a number, indicating the order with which the Oracle database selects rows from a table or a group of joined rows. The value of ROWNUM in the first row is 1, the value of ROWNUM in the second row is 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
        );