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