Netezza语法迁移-华为云

数据仓库服务 GAUSSDB(DWS)-系统函数（Netezza）:存储过程

存储过程 Netezza语法迁移后语法 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE OR REPLACE PROCEDURE sp_ntz_proc_call ( 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'; D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0); RETURN O_RETURN; END; END_PROC; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE OR REPLACE FUNCTION sp_ntz_proc_call ( CHARACTER VARYING(8) ) RETURN INTEGER AS V_PAR_DAY ALIAS for $1; V_PRCNAME NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H'; D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0); RETURN O_RETURN; END; /

数据仓库服务 GAUSSDB(DWS) Netezza语法迁移

数据仓库服务 GAUSSDB(DWS)-系统函数（Netezza）:DATE

DATE 日期类型转换。 Netezza语法迁移后语法 1 2 3 4 5 6 7 8 9 10 SELECT A1.ETL_DATE, A1.MARKET_CODE , A1.DECLARATION_DT , ROW_NUMBER() OVER(PARTITION BY A1.MARKET_CODE, A1.STOCK_CODE, DATE_PART('YEAR', DATE(A1.DECLARATION_DT)) ORDER BY A1.DECLARATION_DT DESC) AS RN FROM tb_date_type_casting A1; SELECT A1.ETL_DATE, A1.MARKET_CODE , A1.DECLARATION_DT , ROW_NUMBER() OVER(PARTITION BY A1.MARKET_CODE, A1.STOCK_CODE, DATE_PART('YEAR', DATE(A1.DECLARATION_DT)) ORDER BY A1.DECLARATION_DT DESC) AS RN FROM tb_date_type_casting A1; 1 2 3 4 SELECT A1.ETL_DATE, A1.MARKET_CODE , A1.DECLARATION_DT , ROW_NUMBER() OVER(PARTITION BY A1.MARKET_CODE, A1.STOCK_CODE, DATE_PART('YEAR', CAST(A1.DECLARATION_DT AS DATE)) ORDER BY A1.DECLARATION_DT DESC) AS RN FROM tb_date_type_casting A1;

数据仓库服务 GAUSSDB(DWS) Netezza语法迁移

数据仓库服务 GAUSSDB(DWS)-系统函数（Netezza）:分析函数（analytic_function）

分析函数（analytic_function） Netezza语法迁移后语法 1 2 3 4 5 6 7 8 9 10 11 SELECT COALESCE(NULLIF(GROUP_CONCAT(a.column_name),''),'*') FROM (SELECT a.column_name FROM tb_ntz_group_concat a WHERE UPPER(a.table_name) = 'EMP' ORDER BY a.column_pos) a; ------------- SELECT admin.group_concat('"top'||lpad(a.table_name,2,'0')||'":{'||a.column_name||'}') topofund_data FROM (SELECT a.table_name, a.column_name FROM tb_ntz_group_concat a WHERE UPPER(a.table_name) = 'EMP' ORDER BY a.column_pos) a; 1 2 3 4 5 6 7 8 9 10 11 SELECT COALESCE(NULLIF(STRING_AGG(a.column_name, ','),''),'*') FROM (SELECT a.column_name FROM tb_ntz_group_concat a WHERE UPPER(a.table_name) = 'EMP' ORDER BY a.column_pos) a; ------------- SELECT STRING_AGG('"top'||lpad(a.table_name,3,'0')||'":{'||a.column_name||'}', ',') topofund_data FROM (SELECT a.table_name, a.column_name FROM tb_ntz_group_concat a WHERE UPPER(a.table_name) = 'EMP' ORDER BY a.column_pos) a;

数据仓库服务 GAUSSDB(DWS) Netezza语法迁移

数据仓库服务 GAUSSDB(DWS)-系统函数（Netezza）:ISNULL()

ISNULL() Netezza语法迁移后语法 1 2 3 4 5 SELECT A.ETL_DATE, A.BRANCH_CODE, A.CUST_NO , ISNULL ( B.RES_STOCK,0) AS RES_STOCK , ISNULL ( B.ZY_VOL ,0 ) AS ZY_VOL , ISNULL ( B.ZJ_VOL,0 ) AS ZJ_VOL FROM tab123; 1 2 3 4 5 SELECT A.ETL_DATE, A.BRANCH_CODE, A.CUST_NO , NVL ( B.RES_STOCK,0) AS RES_STOCK , NVL ( B.ZY_VOL ,0 ) AS ZY_VOL , NVL ( B.ZJ_VOL,0 ) AS ZJ_VOL FROM tab123;

数据仓库服务 GAUSSDB(DWS) Netezza语法迁移

数据仓库服务 GAUSSDB(DWS)-Unique Index:Unique Index

Unique Index Netezza语法迁移后语法 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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 CREATE TABLE prod ( prod_no number(6) not null unique, prod_name national character varying(32) not null, prod_desc clob ) DISTRIBUTE ON (prod_no) ORGANIZE ON (prod_no, prod_name) ; ---------- CREATE TABLE prod ( prod_no number(6) not null CONSTRAINT UQ_prod unique, prod_name national character varying(32) not null, prod_desc clob ) DISTRIBUTE ON (prod_no) ORGANIZE ON (prod_no, prod_name) ; ---------- CREATE TABLE prod ( prod_no number(6) not null PRIMARY KEY, prod_name national character varying(32) not null, prod_desc clob ) DISTRIBUTE ON (prod_no) ORGANIZE ON (prod_no, prod_name) ; ---------- CREATE TABLE prod ( prod_no number(6) not null, prod_name national character varying(32) not null, prod_desc clob, constraint uq_prod UNIQUE (prod_no) ) DISTRIBUTE ON (prod_no) ORGANIZE ON (prod_no, prod_name) ; ---------- CREATE TABLE prod ( prod_no number(6) not null, prod_name national character varying(32) not null, prod_desc clob ) DISTRIBUTE ON (prod_no) ORGANIZE ON (prod_no, prod_name) ; ALTER TABLE prod ADD constraint uq_prod UNIQUE (prod_no); 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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 CREATE TABLE prod ( prod_no number(6) not null /* unique */, prod_name national character varying(32) not null, prod_desc clob ) WITH(ORIENTATION=COLUMN) DISTRIBUTE BY HASH (prod_no) /* ORGANIZE ON (prod_no, prod_name) */ ; ---------- CREATE TABLE prod ( prod_no number(6) not null /* CONSTRAINT UQ_prod unique */, prod_name national character varying(32) not null, prod_desc clob ) WITH(ORIENTATION=COLUMN) DISTRIBUTE BY HASH (prod_no) /* ORGANIZE ON (prod_no, prod_name) */ ; ---------- CREATE TABLE prod ( prod_no number(6) not null /* PRIMARY KEY */, prod_name national character varying(32) not null, prod_desc clob ) WITH(ORIENTATION=COLUMN) DISTRIBUTE BY HASH (prod_no) /* ORGANIZE ON (prod_no, prod_name) */ ; ---------- CREATE TABLE prod ( prod_no number(6) not null, prod_name national character varying(32) not null, prod_desc clob /*, constraint uq_prod UNIQUE (prod_no) */ ) WITH(ORIENTATION=COLUMN) DISTRIBUTE BY HASH (prod_no) /* ORGANIZE ON (prod_no, prod_name)*/ ; ---------- CREATE TABLE prod ( prod_no number(6) not null, prod_name national character varying(32) not null, prod_desc clob ) DISTRIBUTE BY HASH (prod_no) /*ORGANIZE ON (prod_no, prod_name)*/ ; /* ALTER TABLE prod ADD constraint uq_prod UNIQUE (prod_no); */ 仅适用于COLUMN store。对于ROW存储，不应注释“唯一索引”。

数据仓库服务 GAUSSDB(DWS) Netezza语法迁移

云服务器内容精选

Netezza语法迁移

7*24

备案

专业服务

退订

建议反馈

售前咨询热线