Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> Teradata Syntax Migration> View Migration
View PDF

View Migration

CREATE VIEW (short key CV) is used with SELECT to create a new view.

VIEW keyword is supported in both Teradata and GaussDB T, GaussDB A, and GaussDB(DWS), but the SELECT statements are enclosed in braces during the migration process. For more information, refer the following figures.

Use the tdMigrateVIEWCHECKOPTION configuration parameter to configure migration of views containing the WITH CHECK OPTION keyword. If tdmigrateVIEWCHECKOPTION is set to false, the tool will skip migration of the query and will log a message.

If the CREATE VIEW includes the LOCK keyword, then the VIEW query will be migrated based on the value of tdMigrateLOCKoption.

Input - CREATE VIEW

CREATE VIEW DP_STEDW.MY_PARAM 
AS 
SELECT RUNDATE FROM  DP_STEDW.DATE_TBL   WHERE   dummy = 1;

Output

CREATE OR REPLACE VIEW DP_STEDW.MY_PARAM 
AS 
SELECT RUNDATE
  FROM  DP_STEDW.DATE_TBL
        WHERE   dummy = 1;

Input :CREATE VIEW WITH FORCE KEYWORD

CREATE 
OR REPLACE FORCE VIEW IS2010_APP_INFO (
  APP_ID, APP_SHORTNAME, APP_CHNAME, 
  APP_ENNAME
) AS 
select 
  t.app_id, 
  t.app_shortname, 
  t.app_chname, 
  t.app_enname 
from 
  newdrms.seas_app_info t 
WHERE 
  t.app_status <> '2';

Output

CREATE
OR REPLACE
/*FORCE*/
VIEW IS2010_APP_INFO (
    APP_ID,
    APP_SHORTNAME,
    APP_CHNAME,
    APP_ENNAME ) AS
SELECT
    t.app_id,
    t.app_shortname,
    t.app_chname,
    t.app_enname
FROM
    newdrms.seas_app_info t
WHERE
t.app_status <> '2';

REPLACE VIEW

In Teradata, REPLACE VIEW statement creates a new view, or re-creates the existing view. DSC migrates this to the compatible CREATE OR REPLACE VIEW in GaussDB T, GaussDB A, and GaussDB(DWS).

Input - REPLACE VIEW

REPLACE VIEW DP_STEDW.MY_PARAM AS SELECT
          RUNDATE
     FROM
          DP_STEDW.DATE_TBL
     WHERE
          dummy = 1
;

Output

CREATE
OR REPLACE VIEW DP_STEDW.MY_PARAM AS (
     SELECT
               RUNDATE
          FROM
               DP_STEDW.DATE_TBL
          WHERE
               dummy = 1
)
;

Input - REPLACE RECURSIVE VIEW

Replace RECURSIVE VIEW reachable_from (
emp_id,emp_name,DEPTH) 
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL);

Output

CREATE OR REPLACE VIEW reachable_from AS (
WITH RECURSIVE reachable_from (
emp_id,emp_name,DEPTH) 
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL
) SELECT * FROM reachable_from);

Replace Function

Input

REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE 
LANGUAGE SQL 
CONTAINS SQL 
DETERMINISTIC 
SQL SECURITY DEFINER 
COLLATION INVOKER 
INLINE TYPE 1 
RETURN DATE'2017-08-22';

Output

CREATE OR REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
SECURITY DEFINER
AS 
$$
SELECT CAST('2017-08-20' AS DATE)
$$
;

CHECK OPTION

Use the tdMigrateVIEWCHECKOPTION configuration parameter to configure migration of views containing the CHECK OPTION keyword

If a view with CHECK OPTION is present in the source, then the CHECK OPTION is commented from the target database.

Input - VIEW with CHECK OPTION

CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

Output (tdMigrateVIEWCHECKOPTION=True)

CREATE
     OR REPLACE VIEW mgr15 AS (
          SELECT
                    *
               FROM
                    employee
               WHERE
                    manager_id = 15 /*WITH CHECK OPTION */
     )
;

Output (tdMigrateVIEWCHECKOPTION=False)

CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

VIEW WITH RECURSIVE

The Teradata format for RECURSIVE VIEW keyword is unsupported in GaussDB T, GaussDB A, and GaussDB(DWS). Therefore the keyword is replaced with VIEW WITH RECURSIVE keyword as shown in the following figures.

Figure 1 Input view-CREATE RECURSIVE VIEW
Figure 2 Output view

VIEW WITH ACCESS LOCK

Use the tdMigrateLOCKOption configuration parameter to configure migration of query containing the LOCK keyword. If tdMigrateLOCKOption is set to false, the tool will skip migration of the query and will log a message.

Input - VIEW with ACCESS LOCK

CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN 
 AS 
 LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS  FOR ACCESS 
 ( SELECT RUN_ID, PRODUCT_ID, CURRENCY
            , CASHFLOW, ENTITY, LCR
            , TIME_BUCKET, MT, Ctl_Id
            , File_Id, Business_Date
      FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;

Output

CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN 
 AS 
/* LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS */
 ( SELECT RUN_ID, PRODUCT_ID, CURRENCY
            , CASHFLOW, ENTITY, LCR
            , TIME_BUCKET, MT, Ctl_Id
            , File_Id, Business_Date
      FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;