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

Procedure

Variable Data Type

NVARCHAR changed to NCHAR VARING.

Netezza Syntax

Syntac 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.

NetezzaSyntax

Syntac 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;
/

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

Syntac 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;

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.

NetezzaSyntax

Syntac 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.

NetezzaSyntax

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

If it contains a single parameter, just excute 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)

NetezzaSyntax

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

NetezzaSyntax

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

NetezzaSyntax

Syntac 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

NetezzaSyntax

Syntac 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 /

NetezzaSyntax

Syntac 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

NetezzaSyntax

Syntac 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

NetezzaSyntax

Syntac 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

Syntac 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

Syntac 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

Syntac 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;
/
Parent topic: Netezza Syntax Migration