Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ SQL Syntax Migration Reference/ Teradata Syntax Migration/ Data Manipulation Language (DML)/ ACTIVITYCOUNT
Updated on 2024-07-19 GMT+08:00
View PDF
Share

ACTIVITYCOUNT

ACTIVITYCOUNT

Input

It is a status variable that returns the number of rows affected by an SQL DML statement in an embedded SQL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
SEL tablename
FROM dbc.tables
WHERE databasename ='tera_db'
  AND tablename='tab1';

.IF ACTIVITYCOUNT > 0 THEN .GOTO NXTREPORT;
CREATE MULTISET TABLE tera_db.tab1
        , NO FALLBACK
        , NO BEFORE JOURNAL
        , NO AFTER JOURNAL
        , CHECKSUM = DEFAULT
          (
                    Tx_Zone_Num CHAR( 4 )
                  , Tx_Org_Num  VARCHAR( 30 )
          )
          PRIMARY INDEX
          (
                    Tx_Org_Num
          )
          INDEX
          (
                    Tx_Teller_Id
          )
;

.LABEL NXTREPORT
DEL FROM tera_db.tab1;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
DECLARE v_verify TEXT ;
v_no_data_found NUMBER ( 1 ) ;

BEGIN
     BEGIN
          v_no_data_found := 0 ;

          SELECT
                    mig_td_ext.vw_td_dbc_tables.tablename INTO v_verify
               FROM
                    mig_td_ext.vw_td_dbc_tables
               WHERE
                    mig_td_ext.vw_td_dbc_tables.schemaname = 'tera_db'
                    AND mig_td_ext.vw_td_dbc_tables.tablename = 'tab1' ;

               EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                    v_no_data_found := 1 ;

     END ;

     IF
          v_no_data_found = 1 THEN
               CREATE TABLE tera_db.tab1 (
                    Tx_Zone_Num CHAR( 4 )
                    ,Tx_Org_Num VARCHAR( 30 )
               ) DISTRIBUTE BY HASH ( Tx_Org_Num ) ;

     CREATE
          INDEX
               ON tera_db.tab1 ( Tx_Teller_Id ) ;

     END IF ;

     DELETE FROM
          tera_db.tab1 ;

END ;
/