DML

INSERT

The Teradata INSERT (short key INS) statement is used to insert records into a table. DSC supports the INSERT statement.

The INSERT INTO TABLE table_name syntax exists in Teradata SQL, but is not supported by GaussDB(DWS). GaussDB(DWS) supports only the INSERT INTO table_name syntax. Therefore, remove the keyword TABLE when using DSC.

Input

INSERT TABLE tab1 
SELECT col1, col2 
  FROM tab2 
 WHERE col3 > 0;

Output

INSERT INTO tab1 
SELECT col1, col2 
  FROM tab2 
 WHERE col3 > 0;

SELECT

1. ANALYZE

   The Teradata SELECT command (short key SEL) is used to specify the table columns from which data is to be retrieved.

   ANALYZE is used in GaussDB(DWS) for collecting optimizer statistics, which is used for improving query performance.

   Input: ANALYZE with INSERT

   1 2	INSERT INTO employee(empno,ename) Values (1,'John'); COLLECT STAT on employee;

   Output

   1 2 3

   INSERT INTO employee( empno, ename) SELECT 1 ,'John'; ANALYZE employee;

   Input: ANALYZE with UPDATE

   1 2 3

   UPD employee SET ename = 'Jane' WHERE ename = 'John'; COLLECT STAT on employee;

   Output

   1 2 3

   UPDATE employee SET ename = 'Jane' WHERE ename = 'John'; ANALYZE employee;

   Input: ANALYZE with DELETE

   1 2	DEL FROM employee WHERE ID > 10; COLLECT STAT on employee;

   Output

   1 2	DELETE FROM employee WHERE ID > 10; ANALYZE employee;

2. Order of Clausses

   For Teradata migration of SELECT statements, all the clauses (FROM, WHERE, HAVING and GROUP BY) can be listed in any order. The tool will not migrate the statement if it contains a QUALIFY as an ALIAS before the FROM clause.

   Use the tdMigrateALIAS configuration parameter to configure migration of ALIAS.

   Input: Order of Clauses

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   SELECT expr1 AS alias1 , expr2 AS alias2 , expr3 AS alias3 , MAX( expr4 ), ... FROM tab1 T1 INNER JOIN tab2 T2 ON T1.c1 = T2.c2 ... AND T3.c5 = '010' AND ... WHERE T1.c7 = '000' AND ... HAVING alias1 <> 'IC' AND alias2 <> 'IC' AND alias3 <> '' GROUP BY 1, 2, 3 ;

   Output

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   SELECT expr1 AS alias1 , expr2 AS alias2 , expr3 AS alias3 , MAX( expr4 ), ... FROM tab1 T1 INNER JOIN tab2 T2 ON T1.c1 = T2.c2 ... AND T3.c5 = '010' AND ... WHERE T1.c7 = '000' AND ... GROUP BY 1 ,2 ,3 HAVING expr1 <> 'IC' AND expr2 <> 'IC' AND expr3 <> '';

   Input: Order of Clauses

   1 2 3 4 5 6 7 8

   SELECT TOP 10 * GROUP BY DeptNo WHERE empID < 100 FROM tbl_employee;

   Output

   1 2 3 4 5 6 7 8 9

   SELECT * FROM tbl_employee WHERE empID < 100 GROUP BY DeptNo LIMIT 10 ;

   

   If the input script contains QUALIFY as an ALIAS before the FROM clause, the DSC will not migrate the statement and copy the input statement verbatim.

   Input: Order of Clauses with QUALIFY as an ALIAS before the FROM clause

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   SELECT * FROM table1 WHERE abc = ( SELECT col1 AS qualify FROM TABLE WHERE col1 = 5 ) ;

   Output

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   SELECT * FROM table1 WHERE abc = ( SELECT col1 AS qualify FROM TABLE WHERE col1 = 5 ) ;

3. Extended Group by Clause

   The GROUP BY clause can be specified if you want the database to group the selected rows based on the value of expr(s). If this clause contains CUBE, ROLLUP or GROUPING SETS extensions, then the database produces super-aggregate groupings in addition to the regular groupings. These features are not available in GaussDB(DWS), but similar functions can be enabled using the UNION ALL operator.

   Use the extendedGroupByClause configuration parameter to configure migration of the extended GROUP BY clause.

   Input: Extended Group By Clause - CUBE

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   SELECT expr1 AS alias1 , expr2 AS alias2 , expr3 AS alias3 , MAX( expr4 ), ... FROM tab1 T1 INNER JOIN tab2 T2 ON T1.c1 = T2.c2 ... AND T3.c5 = '010' AND ... WHERE T1.c7 = '000' AND ... HAVING alias1 <> 'IC' AND alias2 <> 'IC' AND alias3 <> '' GROUP BY 1, 2, 3 ;

   Output

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   SELECT expr1 AS alias1 , expr2 AS alias2 , expr3 AS alias3 , MAX( expr4 ), ... FROM tab1 T1 INNER JOIN tab2 T2 ON T1.c1 = T2.c2 ... AND T3.c5 = '010' AND ... WHERE T1.c7 = '000' AND ... GROUP BY 1 ,2 ,3 HAVING expr1 <> 'IC' AND expr2 <> 'IC' AND expr3 <> '';

   Input: Extended Group By Clause - ROLLUP

   1 2 3 4 5

   SELECT d.dname, e.job, MAX(e.sal) FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno WHERE e.job IS NOT NULL GROUP BY ROLLUP (d.dname, e.job);

   Output

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   SELECT dname, job, ColumnAlias1 FROM ( SELECT MAX(e.sal) AS ColumnAlias1, d.dname, e.job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY d.dname ,e.job UNION ALL SELECT MAX(e.sal) AS ColumnAlias1, d.dname, NULL AS job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY d.dname UNION ALL SELECT MAX( e.sal ) AS ColumnAlias1, NULL AS dname, NULL AS job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL );

   Input: Extended Group By Clause - GROUPING SETS

   1 2 3 4 5

   SELECT d.dname, e.job, MAX(e.sal) FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno WHERE e.job IS NOT NULL GROUP BY GROUPING SETS(d.dname, e.job);

   Output

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   SELECT dname, job, ColumnAlias1 FROM ( SELECT MAX(e.sal) AS ColumnAlias1 , d.dname, NULL AS job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY d.dname UNION ALL SELECT MAX(e.sal) AS ColumnAlias1 , NULL AS dname, e.job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY e.job );

4. TOP and SAMPLE

   The TOP and SAMPLE clauses of Teradata are migrated to LIMIT in GaussDB(DWS).

   1. TOP

      The DSC also supports migration of TOP statements with dynamic parameters.

      

      • For TOP clauses containing WITH TIES, the ORDER BY clause is also required. Otherwise, the tool will not migrate the statement and copy it as it is.
      • When using TOP with dynamic parameters:
        • The input dynamic parameters should be in the following form:

          1	TOP :<parameter_name>

          The following characters are valid for dynamic parameters: a-z, A-Z, 0-9 and "_".

      Input: SELECT .. TOP

      1 2 3 4

      SELECT TOP 1 c1, COUNT (*) cnt FROM tab1 GROUP BY c1 ORDER BY cnt;

      Output

      1 2 3 4 5

      SELECT c1, COUNT( * ) cnt FROM tab1 GROUP BY c1 ORDER BY cnt LIMIT 1;

      Input: SELECT .. TOP PERCENT

      1 2 3 4

      SELECT TOP 10 PERCENT c1, c2 FROM employee WHERE ... ORDER BY c2 DESC;

      Output

      1 2 3 4 5 6 7 8 9 10

      WITH top_percent AS ( SELECT c1, c2 FROM employee WHERE ... ORDER BY c2 DESC ) SELECT * FROM top_percent LIMIT (SELECT CEIL(COUNT( * ) * 10 / 100) FROM top_percent);

      Input: SELECT .. TOP with dynamic parameters

      1 2 3 4 5 6 7 8 9 10 11 12

      SELECT TOP :Limit WITH TIES c1 ,SUM (c2) sc2 FROM tab1 WHERE c3 > 10 GROUP BY c1 ORDER BY c1 ;

      Output

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

      WITH top_ties AS ( SELECT c1 ,SUM (c2) sc2 ,rank ( ) OVER( ORDER BY c1 ) AS TOP_RNK FROM tab1 WHERE c3 > 10 GROUP BY c1 ) SELECT c1 ,sc2 FROM top_ties WHERE TOP_RNK <= :Limit ORDER BY TOP_RNK ;

      Input: SELECT .. TOP with dynamic parameters and with TIES

      1 2 3 4 5 6 7

      SELECT TOP :Limit WITH TIES Customer_ID FROM Customer_t ORDER BY Customer_ID ;

      Output

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

      WITH top_ties AS ( SELECT Customer_ID ,rank ( ) OVER( order by Customer_id) AS TOP_RNK FROM Customer_t ) SELECT Customer_ID FROM top_ties WHERE TOP_RNK <= :Limit ORDER BY TOP_RNK ;

      Input: SELECT .. TOP PERCENT with dynamic parameters

      1 2 3 4 5 6 7 8 9 10

      SELECT TOP :Input_Limit PERCENT WITH TIES c1 ,SUM (c2) sc2 FROM tab1 GROUP BY c1 ORDER BY c1 ;

      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

      WITH top_percent_ties AS ( SELECT c1 ,SUM (c2) sc2 ,rank ( ) OVER( ORDER BY c1 ) AS TOP_RNK FROM tab1 GROUP BY c1 ) SELECT c1 ,sc2 FROM top_percent_ties WHERE TOP_RNK <= ( SELECT CEIL(COUNT( * ) * :Input_Limit / 100) FROM top_percent_ties ) ORDER BY TOP_RNK ;

   2. SAMPLE
      

      The tool only supports single positive integers in the SAMPLE clause.

      Input: SELECT .. SAMPLE

      1 2 3 4

      SELECT c1, c2, c3 FROM tab1 WHERE c1 > 1000 SAMPLE 1;

      Output

      1 2 3 4

      SELECT c1, c2, c3 FROM tab1 WHERE c1 > 1000 LIMIT 1;

UPDATE

The tool supports and migrates the UPDATE (short key UPD) statements.

Input: UPDATE with TABLE ALIAS

1
2
3
4
5

UPDATE T1
  FROM tab1 T1, tab2 T2
   SET c1 = T2.c1                
     , c2 = T2.c2 
 WHERE T1.c3 = T2.c3;

Output

1
2
3
4
5

UPDATE tab1 T1
   SET c1 = T2.c1                
     , c2 = T2.c2 
  FROM tab2 T2
 WHERE T1.c3 = T2.c3;

Input: UPDATE with TABLE ALIAS using a sub query

1
2
3
4
5

UPDATE t1
  FROM tab1 t1, ( SELECT c1, c2 FROM tab2
                   WHERE c2 > 100 ) t2
   SET c1 = t2.c1
 WHERE t1.c2 = t2.c2;

Output

1
2
3
4
5

 UPDATE tab1 t1
   SET c1 = t2.c1
  FROM ( SELECT c1, c2 FROM tab2
          WHERE c2 > 100 ) t2
 WHERE t1.c2 = t2.c2;

Input: UPDATE with ANALYZE

1
2
3

UPD employee SET ename = 'Jane'
        WHERE ename = 'John';
COLLECT STAT on employee;

Output

1
2
3

UPDATE employee SET ename = 'Jane'
 WHERE ename = 'John';
ANALYZE employee;

DELETE

DELETE (short key abbreviated as DEL) is an ANSI-compliant SQL syntax operator used to delete existing records from a table. DSC supports the Teradata DELETE statement and its short key DEL. The DELETE statement that does not contain the WHERE clause is migrated to TRUNCATE in GaussDB(DWS). Use the deleteToTruncate parameter to enable or disable this behavior.

Input: DELETE

1
2

DEL FROM tab1
 WHERE a =10;

Output

1
2

DELETE FROM tab1
 WHERE a =10;

Input: DELETE without WHERE - Migrated to TRUNCATE if deletetoTruncate=TRUE

1

DELETE FROM ${schemaname} . "tablename" ALL;

Output

1
2
3

TRUNCATE
     TABLE
          ${schemaname} . "tablename";

In DELETE, the same table is used in DELETE and FROM clauses with / without WHERE clause

Input

DELETE DP_TMP.M_P_TX_SCV_REMAINING_PARTY 
FROM DP_TMP.M_P_TX_SCV_REMAINING_PARTY ALL ;
---
DELETE DP_VMCTLFW.CTLFW_Process_Id 
FROM DP_VMCTLFW.CTLFW_Process_Id 
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );
---
DELETE CPID 
FROM DP_VMCTLFW.CTLFW_Process_Id AS CPID
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );

Output

DELETE FROM DP_TMP.M_P_TX_SCV_REMAINING_PARTY;
---
DELETE FROM DP_VMCTLFW.CTLFW_Process_Id 
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );
---
DELETE FROM DP_VMCTLFW.CTLFW_Process_Id AS CPID
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );

DELETE table_alias FROM table

Input

SQL_Detail10124.sql
delete a
  from ${BRTL_DCOR}.BRTL_CS_POT_CUST_UMPAY_INF_S as a
 where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') 
 and a.DW_Job_Seq = 1 ;
was migrated as below:
      DELETE FROM
           BRTL_DCOR.BRTL_CS_POT_CUST_UMPAY_INF_S AS a
                USING
      WHERE a.DW_Snsh_Dt = CAST( lv_mig_v_Trx_Dt AS DATE )
           AND a.DW_Job_Seq = 1 ;
SQL_Detail10449.sql
delete a
  from ${BRTL_DCOR}.BRTL_EM_YISHITONG_USR_INF as a
 where a.DW_Job_Seq = 1 ;
was migrated as below:
      DELETE FROM
           BRTL_DCOR.BRTL_EM_YISHITONG_USR_INF AS a
                USING
      WHERE a.DW_Job_Seq = 1 ;
SQL_Detail5742.sql
delete a
  from ${BRTL_DCOR}.BRTL_PD_FP_NAV_ADT_INF as a;
was migrated as 
      DELETE a
 FROM
      BRTL_DCOR.BRTL_PD_FP_NAV_ADT_INF AS a ;

Output

SQL_Detail10124.sql
delete from ${BRTL_DCOR}.BRTL_CS_POT_CUST_UMPAY_INF_S as a
 where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') 
 and a.DW_Job_Seq = 1 ;
SQL_Detail10449.sql
delete from ${BRTL_DCOR}.BRTL_EM_YISHITONG_USR_INF as a
 where a.DW_Job_Seq = 1 ;
SQL_Detail5742.sql
delete from ${BRTL_DCOR}.BRTL_PD_FP_NAV_ADT_INF as a;

MERGE

Gauss database in 6.5.0 or later versions support the MERGE function.

MERGE is an ANSI-standard SQL syntax operator used to select rows from one or more sources for updating or inserting into a table or view. The conditions to update or insert to the target table or view can be specified.

Input: MERGE

1
2
3
4
5
6
7
8

MERGE INTO tab1 A
using ( SELECT c1, c2, ... FROM tab2 WHERE ...) AS B
ON A.c1 = B.c1
 WHEN MATCHED THEN 
   UPDATE SET c2 = c2
            , c3 = c3
  WHEN NOT MATCHED THEN 
INSERT VALUES (B.c1, B.c2, B.c3);

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

WITH B AS (
     SELECT
               c1
               ,c2
               ,...
          FROM
               tab2
          WHERE
               ...
)
,UPD_REC AS (
     UPDATE
               tab1 A
          SET
               c2 = c2
               ,c3 = c3
          FROM
               B
          WHERE
               A.c1 = B.c1 returning A. *
)
INSERT
     INTO
          tab1 SELECT
                    B.c1
                    ,B.c2
                    ,B.c3
                 FROM
                    B
                WHERE
                    NOT EXISTS (
                         SELECT
                                 1
                           FROM
                                 UPD_REC A
                          WHERE
                                 A.c1 = B.c1
                               )
; 

NAMED

NAMED is used in Teradata to assign a temporary name to an expression or column. The NAMED statements for expression names are migrated to AS in GaussDB(DWS). The NAMED statements for column names are retained in the same syntax.

Input: NAMED Expression migrated to AS

1
2
3

SELECT Name, ((Salary + (YrsExp * 200))/12) (NAMED Projection)
  FROM Employee
 WHERE DeptNo = 600 AND Projection < 2500;

Output

1
2
3

SELECT Name, ((Salary + (YrsExp * 200))/12) AS  Projection
  FROM Employee
 WHERE DeptNo = 600 AND ((Salary + (YrsExp * 200))/12)  < 2500;

Input: NAMED AS for Column Name

1
2

SELECT product_id AS id
  FROM emp where pid=2 or id=2;

Output

1
2

SELECT product_id (NAMED "pid") AS id
  FROM emp where product_id=2 or product_id=2;

Input: NAMED( ) for Column Name

1

INSERT INTO Neg100 (NAMED,ID,Dept) VALUES ('TEST',1,'IT');

Output

1

INSERT INTO Neg100 (NAMED,ID,Dept) SELECT 'TEST',1, 'IT';

Input: NAMED alias with TITLE alias without AS

1
2
3

SELECT dept_name (NAMED alias1) (TITLE alias2 ) 
  FROM employee 
 WHERE dept_name like 'Quality';

Output

1
2
3
4

SELECT dept_name 
    AS alias1 
  FROM employee
 WHERE dept_name like 'Quality';

Input: NAMED alias with TITLE alias with AS

The DSC will skip the NAMED alias and TITLE alias and use only the AS alias.

1
2
3
4

SELECT sale_name (Named alias1 ) (Title alias2) 
    AS alias3 
  FROM employee 
 WHERE sname = 'Stock' OR sname ='Sales';

Output

1
2
3
4

SELECT sale_name 
    AS alias3 
  FROM employee 
 WHERE sname = 'Stock' OR sname ='Sales';

Input: NAMED with TITLE

NAMED and TITLE used together, separated by comma(,) within brackets().

1
2
3

SELECT customer_id (NAMED cust_id, TITLE 'Customer Id')
FROM Customer_T 
WHERE cust_id > 10;

Output

1
2
3
4

SELECT cust_id AS "Customer Id" 
FROM   (SELECT customer_id AS cust_id 
                FROM   customer_t 
                WHERE  cust_id > 10); 

ACTIVITYCOUNT

Input

It's 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 ;
/

TIMESTAMP

Input - TIMESTAMP with FORMAT

The FORMAT phrase sets the format for a specific TIME or TIMESTAMP column or value. A FORMAT phrase overrides the system format.

1
2

SELECT 'StartDTTM' as a
             ,CURRENT_TIMESTAMP (FORMAT 'HH:MI:SSBMMMBDD,BYYYY');

Output

1
2

SELECT 'StartDTTM' AS a 
             ,TO_CHAR( CURRENT_TIMESTAMP ,'HH:MI:SS MON DD, YYYY' ) ;

TIMESTAMP Types Casting

Input

COALESCE( a.Snd_Tm ,TIMESTAMP '0001-01-01 00:00:00' )
should be migrated as below:
COALESCE( a.Snd_Tm , CAST('0001-01-01 00:00:00' AS TIMESTAMP) )

Output

COALESCE( a.Snd_Tm , CAST('0001-01-01 00:00:00' AS TIMESTAMP) )