Updated on 2023-04-03 GMT+08:00

DML

This section describes the syntax for migrating Teradata DML. The migration syntax determines how the keywords and features are migrated.

In Teradata, SQL queries in a file that contains the SELECT, INSERT, UPDATE, DELETE, or MERGE statement can be migrated to GaussDB(DWS).

For details, see the following topics:

INSERT

SELECT

UPDATE

DELETE

MERGE

NAMED

ACTIVITYCOUNT

TIMESTAMP

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) )