我有一个带有公式列的表,该表具有可获取到动态 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,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