时间戳

首页 > 解决方案 > Oracle为动态SQL创建公式以防止被零除

sql - Oracle为动态SQL创建公式以防止被零除

问题描述

我有一个带有公式列的表，其中包含要获取到动态 SQL LIKE 的公式n5/n14+n3

我的问题是当公式有划分时，我应该尝试转换公式以防止错误divide by zero。

我目前的想法是将公式转换为当它具有神圣性时，CASE例如n5/n14+n3，CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3 END

但我只能在公式只有 1 个除法的情况下进行转换，并且不能在公式有 > 1 个除法的情况下进行转换（例如n5/n4+n3/n2，或公式除以表达式（例如n5/(n4+n3).

任何人都可以解决这个问题吗？

WITH tmp AS 
(
    SELECT 'n5/n14+n3' AS formula FROM dual UNION ALL 
    SELECT 'n5/n14+n3/n1-n2' AS formula FROM dual UNION ALL 
    SELECT 'n8/(n17*n6)-n5/n4+n3/n1-n2' AS formula FROM dual UNION ALL 
    SELECT 'n5/(n14+n3*n2)-n1' AS formula FROM dual 
)
SELECT
    formula,
     CASE
         WHEN formula NOT LIKE '%/%' OR REPLACE(formula, ' ', '') LIKE '%/(%' OR (LENGTH(formula) - LENGTH(REPLACE(formula, '/', ''))) > 1 THEN  formula
         ELSE 'CASE WHEN ' || SUBSTR(REGEXP_SUBSTR(REPLACE(formula, ' ', ''), '/(n\d+)'), 2) || ' = 0 THEN 0 ELSE ' || formula || ' END'
     END AS formula1,
     'CASE WHEN ' || SUBSTR(REGEXP_SUBSTR(REPLACE(formula, ' ', ''), '/(n\d+)'), 2) || ' = 0 THEN 0 ELSE ' || formula || ' END' AS formula2
 FROM tmp t;

当前结果不是预期输出（仅第 1 行解决除以零错误）：

formula                     formula1                                    formula2
n5/n14+n3                   CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3 END CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3 END                
n5/n14+n3/n1-n2             n5/n14+n3/n1-n2                             CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3/n1-n2 END          
n8/(n17*n6)-n5/n4+n3/n1-n2  n8/(n17*n6)-n5/n4+n3/n1-n2                  CASE WHEN n4 = 0 THEN 0 ELSE n8/(n17*n6)-n5/n4+n3/n1-n2 END
n5/(n14+n3*n2)-n1           n5/(n14+n3*n2)-n1                           CASE WHEN  = 0 THEN 0 ELSE n5/(n14+n3*n2)-n1 END

标签： sqloracledynamic-sqldivide-by-zeroregexp-substr

解决方案

您可以使用它NULLIF()来防止错误。结果将是NULL：

(
    SELECT 'n5/nullif(n14, 0)+n3' AS formula FROM dual UNION ALL 
    SELECT 'n5/nullif(n14, 0)+n3/nullif(n1, 0)-n2' AS formula FROM dual UNION ALL 
    SELECT 'n8/nullif(n17*n6, 0)-n5/nullif(n4, 0)+n3/nullif(n1, 0)-n2' AS formula FROM dual UNION ALL 
    SELECT 'n5/nullif((n14+n3*n2, 0)-n1' AS formula FROM dual 
)

推荐阅读