文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> Netezza语法迁移> 系统函数

系统函数

分享
更新时间:2020/08/05 GMT+08:00

ISNULL()

Netezza语法

迁移后语法

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

NVL

第二个函数丢失。

Netezza语法

迁移后语法

SELECT NVL( SUM(A3.DA_CPTL_BAL_YEAR) / NULLIF(V_YEAR_DAYS, 0) ) AS CPTL_BAL_AVE_YR  
      , NVL( NVL(SUM (CASE WHEN A3.OPENACT_DT >= V_YEAR_START THEN A3.DA_CPTL_BAL_YEAR 
       END) / NULLIF(V_YEAR_DAYS, 0) ), 0) AS CPTL_BAL_AVE_YR_OP  
      , NVL( SUM(A3.DA_CPTL_BAL) / NULLIF(V_YEAR_DAYS, 0) ) AS CPTL_BAL_AVE 
   FROM tab1 A3;
ELECT NVL( SUM(A3.DA_CPTL_BAL_YEAR) / NULLIF(V_YEAR_DAYS, 0), NULL ) AS CPTL_BAL_AVE_YR  
      , NVL( NVL(SUM (CASE WHEN A3.OPENACT_DT >= V_YEAR_START THEN A3.DA_CPTL_BAL_YEAR 
       END) / NULLIF(V_YEAR_DAYS, 0), NULL), 0) AS CPTL_BAL_AVE_YR_OP  
      , NVL( SUM(A3.DA_CPTL_BAL) / NULLIF(V_YEAR_DAYS, 0), NULL ) AS CPTL_BAL_AVE 
   FROM tab1 A3;

DATE

日期类型转换。

Netezza语法

迁移后语法

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

分析函数(analytic_function)

Netezza语法

迁移后语法

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

存储过程

Netezza语法

迁移后语法

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;
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; 
 /
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!非常感谢您的反馈,我们会继续努力做到更好!
反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问