Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Procedure

Updated on 2022-07-29 GMT+08:00

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

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

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback