PROCEDURE with RETURNS will be modified to FUNCTION with RETURN.
|
Netezza Syntax |
Syntax After Migration |
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
|
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
--1.1
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
END_PROC;
|
|
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
|
CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
/* 1.1 */
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
/
|
|
Qualifying Language
Migrate the nzplSQL language to the plpgSQL language or delete the language.
|
Netezza Syntax |
Syntax After Migration |
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
|
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
--1.1
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
END_PROC;
|
|
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
|
CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
/* 1.1 */
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
/
|
|
Process Compilation Specification
The process which is started with Begin_PROC and ended with END_PROC should be removed.
|
Netezza Syntax |
Syntax After Migration |
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
|
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
--1.1
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
END_PROC;
|
|
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
|
CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
/* 1.1 */
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
/
|
|
DECLARE Keyword to Declare the Local Variables
DECLARE should be modified to AS.
|
Netezza Syntax |
Syntax After Migration |
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
|
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
--1.1
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
END_PROC;
|
|
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
|
CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(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';
BEGIN
/* 1.1 */
SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
if V_CNT>0 then
EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
end if;
END;
RETURN O_RETURN;
END;
/
|
|
