Procedure

Variable Data Type

NVARCHAR changed to NCHAR VARING.

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE "NTZDB"."EDW"."SP_NTZ_NVARCHAR"
           (CHARACTER VARYING(8))
 RETURNS INTEGER
 LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE
     V_PAR_DAY ALIAS for $1;
     V_PRCNAME    NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H';
     V_CNT        INTEGER;
     V_STEP_INFO   NVARCHAR(500);
     D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
     O_RETURN INTEGER;
 BEGIN
   O_RETURN := 0;

--Writes logs and starts the recording process.
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running ',' ');

  V_STEP_INFO := '1.Initialization';

  RETURN O_RETURN;

 END;
 END_PROC;
CREATE OR REPLACE FUNCTION "EDW"."SP_NTZ_NVARCHAR"
            (CHARACTER VARYING(8))
 RETURN INTEGER
 AS
     V_PAR_DAY ALIAS for $1;
     V_PRCNAME    NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H';
     V_CNT        INTEGER;
     V_STEP_INFO   NCHAR VARYING(500);
     D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
     O_RETURN INTEGER;
 BEGIN
   O_RETURN := 0;

 /* Writes logs and starts the recording process. */
  SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running',' ');

  V_STEP_INFO := '1.Initialization';

  RETURN O_RETURN;

 END;
 /

row counts

The row_count function is supported for affected row counting.

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE "NTZDB"."EDW"."SP_NTZ_ROWCOUNT"
        (CHARACTER VARYING(8))
 RETURNS INTEGER
 LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE
     V_PAR_DAY ALIAS for $1;
     O_RETURN INTEGER;
 BEGIN
   O_RETURN := 0;

  EXECUTE IMMEDIATE 'INSERT  INTO TMPO_HXYW_LNSACCTINFO_H1
            ( ACCTNO, ACCTYPE, SUBCTRLCODE, CCY, NAME )
               SELECT ACCTNO, ACCTYPE, SUBCTRLCODE, CCY, NAME
                FROM O_HXYW_LNSACCTINFO T
              WHERE NOT EXISTS (SELECT 1 FROM O_HXYW_LNSACCT T1
                              WHERE T1.DATA_START_DT<='''||V_PAR_DAY||'''
                                AND T.MD5_VAL=T1.MD5_VAL)';

  O_RETURN := ROW_COUNT;

  RETURN O_RETURN;

 END;
 END_PROC;
CREATE OR REPLACE FUNCTION "EDW"."SP_NTZ_ROWCOUNT"
            (CHARACTER VARYING(8))
 RETURN INTEGER
 AS
     V_PAR_DAY ALIAS for $1;
     O_RETURN INTEGER;
 BEGIN
    O_RETURN := 0;
     EXECUTE IMMEDIATE 'INSERT  INTO TMPO_HXYW_LNSACCTINFO_H1
            ( ACCTNO, ACCTYPE, SUBCTRLCODE, CCY, NAME )
               SELECT ACCTNO, ACCTYPE, SUBCTRLCODE, CCY, NAME
                FROM O_HXYW_LNSACCTINFO T
              WHERE NOT EXISTS (SELECT 1 FROM O_HXYW_LNSACCT T1
                              WHERE T1.DATA_START_DT<='''||V_PAR_DAY||'''
                                AND T.MD5_VAL=T1.MD5_VAL)';

    O_RETURN := SQL%ROWCOUNT;

    RETURN O_RETURN;

 END;
 /

Note

ROW_COUNT identifies the number of rows associated with the previous SQL statement. If the previous SQL statement is a DELETE, INSERT, or UPDATE statement, ROW_COUNT identifies the number of rows that qualified for the operation.

System Tables

System tables _V_SYS_COLUMNS is replaced with information_schema.columns.

Netezza Syntax

Syntax After Migration

BEGIN
    SELECT COUNT(*) INTO V_CNT FROM _V_SYS_COLUMNS
       WHERE table_schem = 'SCOTT'
          AND TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;
BEGIN
     SELECT COUNT(*) INTO V_CNT FROM information_schema.columns
       WHERE table_schema = lower('SCOTT')
            AND table_name = lower('TMPO_HXYW_LNSACCTINFO_H1');
     if V_CNT>0 then
        EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
     end if;
   END;

Note

Column mapping:

  • table_schem => table_schema

  • table_name => table_name

  • column_name => column_name

  • ordinal_position => ordinal_position

  • type_name => data_type

  • is_nullable => is_nullable

For date subtraction, the corresponding Integer should be returned

Return value should be integer for date subtraction.

Netezza Syntax

Syntax After Migration

SELECT CAST( T1.Buyback_Mature_Dt - CAST( '${gsTXDate}' AS DATE) AS CHAR( 5 ) )
   FROM tab1 T1
 WHERE T1.col1 > 10;
 -----
 SELECT CURRENT_DATE - DATE '2019-03-30';
SELECT CAST( EXTRACT( 'DAY' FROM ( T1.Buyback_Mature_Dt - CAST( '${gsTXDate}' AS DATE ) ) ) AS CHAR( 5 ) )
    FROM tab1 T1
  WHERE T1.col1 > 10;
 -------
 SELECT EXTRACT( 'DAY' FROM (CURRENT_DATE - CAST( '2019-03-30' AS DATE ) ) );

Support of TRANSLATE Function

The SQL TRANSLATE() function replaces a sequence of characters in a string with another sequence of characters. The function replaces a single character at a time.

Netezza Syntax

Syntax After Migration

TRANSLATE(param1)
 TRANSLATE(1st param, 2nd param, 3rd param)
 TRANSLATE(1st param, 2nd param, 3rd param, 4th param)
UPPER(param1)
 TRANSLATE(1st param, 3rd param, RPAD(2nd param, LENGTH(3rd param), ' '))
 TRANSLATE(1st param, 3rd param, RPAD(2nd param, LENGTH(3rd param), 4th param))

Note

If it contains a single parameter, just execute the UPPER.

UPPER(param1)

If it contains two parameters, throw error.

If it contains three parameters, TRANSLATE(1st param, 3rd param, RPAD(2nd param, LENGTH(3rd param), ' ')).

If it contains four parameters, TRANSLATE(1st param, 3rd param, RPAD(2nd param, LENGTH(3rd param), 4th param)).

Data Type

NATIONAL CHARACTER VARYING ( ANY )

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_ntz_nchar_with_any
       ( NATIONAL CHARACTER VARYING(10)
       , NATIONAL CHARACTER VARYING(ANY) )
 RETURN NATIONAL CHARACTER VARYING(ANY)
 LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE
      I_LOAD_DT ALIAS FOR $1 ;
      -- ETL Date
      V_TASK_ID ALIAS FOR $2 ;
 BEGIN
    RETURN I_LOAD_DT || ',' || V_TASK_ID;
 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_nchar_with_any
           ( NATIONAL CHARACTER VARYING(10)
           , NATIONAL CHARACTER VARYING  )
 RETURN NATIONAL CHARACTER VARYING
 AS
      I_LOAD_DT ALIAS FOR $1 ;
      /*  ETL Date */
      V_TASK_ID ALIAS FOR $2 ;
 BEGIN
    RETURN I_LOAD_DT || ',' || V_TASK_ID;
 END;
 /

CHARACTER VARYING ( ANY )

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_ntz_char_with_any
           ( NATIONAL CHARACTER VARYING(10)
           , CHARACTER VARYING(ANY) )
 RETURN CHARACTER VARYING(ANY)
 LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE
      I_LOAD_DT ALIAS FOR $1 ;
      -- ETL Date
      V_TASK_ID ALIAS FOR $2 ;
 BEGIN
    RETURN I_LOAD_DT || ',' || V_TASK_ID;
 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_char_with_any
          ( NATIONAL CHARACTER VARYING(10)
          , CHARACTER VARYING  )
 RETURN CHARACTER VARYING
 AS
      I_LOAD_DT ALIAS FOR $1 ;
      /*  ETL Date */
      V_TASK_ID ALIAS FOR $2 ;
 BEGIN
    RETURN I_LOAD_DT || ',' || V_TASK_ID;
 END;
 /

Numeric (ANY)

Netezza Syntax

Syntax After Migration

CREATE or replace PROCEDURE sp_ntz_numeric_with_any
           ( NUMERIC(ANY)
           , NUMERIC(ANY) )
 RETURNS NATIONAL CHARACTER VARYING(ANY)
 LANGUAGE NZPLSQL
 AS BEGIN_PROC
 DECLARE
   ERROR_INFO NVARCHAR(2000) := '';
   V_VC_YCBZ NVARCHAR(1) := 'N';
   V_VC_SUCCESS NVARCHAR(10) := 'SUCCESS';

   p_l_begindate ALIAS FOR $1;
   p_l_enddate ALIAS FOR $2;
 BEGIN
   ERROR_INFO := CRHSP_CRH_ETL_EXCHDATE(p_l_begindate,p_l_enddate);
   if ERROR_INFO != V_VC_SUCCESS  then
     V_VC_YCBZ := 'C';
   end if;

   RETURN V_VC_SUCCESS;

 END;
 END_PROC;
CREATE or replace FUNCTION  sp_ntz_numeric_with_any
            ( NUMERIC
            , NUMERIC )
 RETURN NATIONAL CHARACTER VARYING
 AS
   ERROR_INFO NCHAR VARYING(2000) := '';
   V_VC_YCBZ NCHAR VARYING(1) := 'N';
   V_VC_SUCCESS NCHAR VARYING(10) := 'SUCCESS';

   p_l_begindate ALIAS FOR $1;
   p_l_enddate ALIAS FOR $2;
 BEGIN
   ERROR_INFO := CRHSP_CRH_ETL_EXCHDATE(p_l_begindate,p_l_enddate);
   if ERROR_INFO != V_VC_SUCCESS  then
     V_VC_YCBZ := 'C';
   end if;

   RETURN V_VC_SUCCESS;

 END;
 /

Exception

TRANSACTION_ABORTED

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_ntz_transaction_aborted
     ( NUMERIC(ANY)
     , NUMERIC(ANY) )
     RETURNS NATIONAL CHARACTER VARYING(ANY)
     LANGUAGE NZPLSQL
 AS BEGIN_PROC
 DECLARE
   ERROR_INFO NVARCHAR(2000) := '';


   p_l_begindate ALIAS FOR $1;
   p_l_enddate ALIAS FOR $2;
 BEGIN
   ERROR_INFO := CRHSP_CRH_ETL_EXCHDATE(p_l_begindate,p_l_enddate);
   RETURN ERROR_INFO;


 EXCEPTION
   WHEN TRANSACTION_ABORTED THEN
   ROLLBACK;
   BEGIN
     ERROR_INFO := SQLERRM||'  sp_o_transaction_aborted:';
     RETURN ERROR_INFO;
   END;


   WHEN OTHERS THEN
   BEGIN
     ERROR_INFO := SQLERRM||'  sp_o_transaction_aborted:';
     RETURN ERROR_INFO;
   END;
 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_transaction_aborted
           ( NUMERIC
           , NUMERIC )
     RETURN NATIONAL CHARACTER VARYING
 AS
   ERROR_INFO NCHAR VARYING(2000) := '';


   p_l_begindate ALIAS FOR $1;
   p_l_enddate ALIAS FOR $2;
 BEGIN
   ERROR_INFO := CRHSP_CRH_ETL_EXCHDATE(p_l_begindate,p_l_enddate);
   RETURN ERROR_INFO;


 EXCEPTION
   WHEN INVALID_TRANSACTION_TERMINATION THEN
   ROLLBACK;
   BEGIN
     ERROR_INFO := SQLERRM||'  sp_o_transaction_aborted:';
     RETURN ERROR_INFO;
   END;


   WHEN OTHERS THEN
   BEGIN
     ERROR_INFO := SQLERRM||'  sp_o_transaction_aborted:';
     RETURN ERROR_INFO;
   END;
 END;
 /

END statement is specified without semicolon (;)

END statement specified without semicolon (;) is migrated as follows:

END /

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_ntz_end_wo_semicolon
      ( NATIONAL CHARACTER VARYING(10) )
 RETURNS CHARACTER VARYING(ANY)
 LANGUAGE NZPLSQL
 AS
 BEGIN_PROC
 DECLARE
    v_B64 Varchar(64) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
    v_I int := 0;
    v_J int := 0;
    v_K int := 0;
    v_N int := 0;
    v_out Numeric(38,0) := 0;
    I_LOAD_DT ALIAS FOR $1;

 BEGIN
     v_N:=Length(v_B64);
     FOR v_I In Reverse 1..Length(IN_base64)
     LOOP
       v_J:=Instr(v_B64,Substr(IN_base64,v_I,1))-1;
       If v_J <0 Then
         RETURN -1;
       End If;
       V_Out:=V_Out+v_J*(v_N**v_K);
       v_K:=v_K+1;
     END LOOP;
     RETURN V_Out;
 END
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_end_wo_semicolon
      ( NATIONAL CHARACTER VARYING(10) )
     RETURN CHARACTER VARYING
 AS
    v_B64 Varchar(64) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
    v_I int := 0;
    v_J int := 0;
    v_K int := 0;
    v_N int := 0;
    v_out Numeric(38,0) := 0;
    I_LOAD_DT ALIAS FOR $1;

   BEGIN
     v_N:=Length(v_B64);
     FOR v_I In Reverse 1..Length(IN_base64)
     LOOP
       v_J:=Instr(v_B64,Substr(IN_base64,v_I,1))-1;
       If v_J <0 Then
         RETURN -1;
       End If;
       V_Out:=V_Out+v_J*(v_N**v_K);
       v_K:=v_K+1;
     END LOOP;
     RETURN V_Out;
   END;
 /

LOOP

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_ntz_for_loop_with_more_dots
      ( INTEGER )
     RETURNS CHARACTER VARYING(ANY)
     LANGUAGE NZPLSQL
 AS BEGIN_PROC
 DECLARE p_abc  INTEGER;
         p_bcd  INTEGER;
         p_var1 ALIAS FOR $1;
 BEGIN
      p_bcd := ISNULL(p_var1, 10);
      RAISE NOTICE 'p_bcd=%', p_bcd;

      FOR p_abc IN 0...(p_bcd)
      LOOP
           RAISE NOTICE 'hello world %', p_abc;
      END LOOP;

 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_for_loop_with_more_dots
      ( INTEGER )
   RETURN CHARACTER VARYING
   AS
         p_abc INTEGER ;
         p_bcd  INTEGER;
         p_var1 ALIAS FOR $1;

 BEGIN
      p_bcd := NVL(p_var1, 10);

      RAISE NOTICE 'p_bcd=%', p_bcd;

      FOR p_abc IN 0..(p_bcd)
      LOOP
           RAISE NOTICE 'hello world %', p_abc;

      END LOOP;

 END;
 /

Gauss keyword

CURSOR

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_ntz_keyword_cursor()
     RETURNS INTEGER
     LANGUAGE NZPLSQL
 AS BEGIN_PROC
 DECLARE
    tablename NVARCHAR(100);
    cursor RECORD;
 BEGIN
    FOR cursor IN SELECT t.TABLENAME FROM _V_TABLE t
                   WHERE TABLENAME LIKE 'T_ODS_CRM%'
    LOOP
         tablename := cursor.TABLENAME;
    END LOOP;
 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_keyword_cursor()
     RETURN INTEGER
 AS
    tablename NCHAR VARYING(100);
    mig_cursor RECORD;
 BEGIN
    FOR mig_cursor IN (SELECT t.TABLENAME FROM _V_TABLE t
                   WHERE TABLENAME LIKE 'T_ODS_CRM%')
    LOOP
         tablename := mig_cursor.TABLENAME;
    END LOOP;
 END;
 /

DECLARE

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_ntz_declare_inside_begin
      ( NATIONAL CHARACTER VARYING(10) )
 RETURNS INTEGER
 LANGUAGE NZPLSQL
 AS BEGIN_PROC
 DECLARE
     I_LOAD_DT ALIAS FOR $1;
 BEGIN
    DECLARE
            MYCUR              RECORD;
            VIEWSQL1           NVARCHAR(4000);
    BEGIN
    FOR MYCUR IN ( SELECT VIEWNAME,VIEWSQL
                        FROM T_DDW_AUTO_F5_VIEW_DEFINE
        WHERE OWNER = 'ODS_PROD' )
          LOOP
               VIEWSQL1 := MYCUR.VIEWSQL;
               WHILE INSTR(VIEWSQL1,'v_p_etldate') > 0
      LOOP
                   VIEWSQL1 := SUBSTR(VIEWSQL1,1,INSTR(VIEWSQL1,'v_p_etldate') - 1)||''''||I_LOAD_DT||''''||SUBSTR(VIEWSQL1,INSTR(VIEWSQL1,'v_p_etldate') + 11);
               END LOOP;


               EXECUTE IMMEDIATE VIEWSQL1;
          END LOOP;
    END;


    RETURN 0;
 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_declare_inside_begin
      ( NATIONAL CHARACTER VARYING(10) )
 RETURN INTEGER
 AS
     I_LOAD_DT ALIAS FOR $1;
 BEGIN
    DECLARE
            MYCUR              RECORD;
            VIEWSQL1           NCHAR VARYING(4000);
    BEGIN
    FOR MYCUR IN ( SELECT VIEWNAME,VIEWSQL
                        FROM T_DDW_AUTO_F5_VIEW_DEFINE
        WHERE OWNER = 'ODS_PROD' )
          LOOP
               VIEWSQL1 := MYCUR.VIEWSQL;
               WHILE INSTR(VIEWSQL1,'v_p_etldate') > 0
      LOOP
                   VIEWSQL1 := SUBSTR(VIEWSQL1,1,INSTR(VIEWSQL1,'v_p_etldate') - 1)||''''||I_LOAD_DT||''''||SUBSTR(VIEWSQL1,INSTR(VIEWSQL1,'v_p_etldate') + 11);
               END LOOP;


               EXECUTE IMMEDIATE VIEWSQL1;
          END LOOP;
    END;


    RETURN 0;
 END;
 /

EXECUTE AS CALLER

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE  sp_ntz_exec_as_caller
      ( CHARACTER VARYING(512) )
     RETURNS INTEGER
     LANGUAGE NZPLSQL
     EXECUTE AS CALLER
 AS BEGIN_PROC
 DECLARE
  SQL ALIAS FOR $1;
 BEGIN
  EXECUTE IMMEDIATE SQL;
  RETURN 0;
 END;
 END_PROC;
 ------------------------
 CREATE or replace PROCEDURE  sp_ntz_exec_as_owner
      ( CHARACTER VARYING(512) )
     RETURNS INTEGER
     LANGUAGE NZPLSQL
     EXECUTE AS OWNER
 AS BEGIN_PROC
 DECLARE
  SQL ALIAS FOR $1;
 BEGIN
  EXECUTE IMMEDIATE SQL;
  RETURN 0;
 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_ntz_exec_as_caller
      ( CHARACTER VARYING(512) )
     RETURN INTEGER
    SECURITY INVOKER
 AS
  SQL ALIAS FOR $1;
 BEGIN
     EXECUTE IMMEDIATE SQL;
     RETURN 0;
 END;
 /
 ------------------------
 CREATE OR REPLACE FUNCTION sp_ntz_exec_as_owner
      ( CHARACTER VARYING(512) )
     RETURN INTEGER
    SECURITY DEFINER
 AS
  SQL ALIAS FOR $1;
 BEGIN
     EXECUTE IMMEDIATE SQL;
     RETURN 0;
 END;
 /

Expression

SELECT result assign into variable.

Netezza Syntax

Syntax After Migration

CREATE OR REPLACE PROCEDURE sp_sel_res_to_var
   ( NATIONAL CHARACTER VARYING(10) )
     RETURNS CHARACTER VARYING(ANY)
     LANGUAGE NZPLSQL
 AS BEGIN_PROC
 DECLARE
      counts INTEGER := 0 ;
      I_LOAD_DT ALIAS FOR $1 ;
 BEGIN

         COUNTS := SELECT COUNT( * )
                     FROM tb_sel_res_to_var
                    WHERE ETLDATE = I_LOAD_DT;

   EXECUTE IMMEDIATE 'insert into TABLES_COUNTS values( ''tb_sel_res_to_var'', ''' || I_LOAD_DT || ''', ' || COUNTS || ')' ;

      RETURN '0' ;
 END;
 END_PROC;
CREATE OR REPLACE FUNCTION sp_sel_res_to_var
   ( NATIONAL CHARACTER VARYING(10) )
     RETURN CHARACTER VARYING
 AS
      counts INTEGER := 0 ;
      I_LOAD_DT ALIAS FOR $1 ;
 BEGIN

         SELECT COUNT(*)
     INTO COUNTS
           FROM tb_sel_res_to_var
          WHERE ETLDATE = I_LOAD_DT;

   EXECUTE IMMEDIATE 'insert into TABLES_COUNTS values( ''tb_sel_res_to_var'', ''' || I_LOAD_DT || ''', ' || COUNTS || ')' ;

      RETURN '0' ;
 END;
 /