Oracle Create formula for dynamic SQL to prevent divide by zero

Question

I have a table with formula column that have formula to fetch to dynamic SQL LIKE n5/n14+n3

My problem is when formula have divide, I should try convert the formula to prevent error divide by zero.

My current idea is convert formula to CASE when it have divine, for e.g n5/n14+n3 to CASE WHEN n14 = 0 THEN 0 ELSE n5/n14+n3 END,

but I can convert only in case formula have only 1 divide, and cannot convert in case formula have > 1 divide ( e.g n5/n4+n3/n2, or formula have divide by an expression (e.g n5/(n4+n3).

Could anyone have solution for that?

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;

Current result is not expected output (only line 1 resolve divide by zero error):

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

Your idea (to show the result 0 in case of division by 0) is terrible. How will you know that there was an error, and the result 0 shouldn't actually be used for anything further? Best to leave that as `null` (or, even better, let the result have an additional column for short error messages, if needed - in this case it would show "divide by zero"). — , Jul 31 '21 at 02:27
@mathguy do you have some idea how to do it? Currently formulas is in a table, and after fetching them I wil run dynamic sql like `EXECUTE IMMEDIATE 'UPDATE temp SET ' || l_cursor.col_name || ' = ' || l_cursor.formula`, and I don't know which row would it rasise error (or maybe luckily, no zero, no error..) and how to handle it when error was thrown. — Pham X. Bach, Jul 31 '21 at 02:45
@mathguy Ah I understand that, If my dynamic SQL `EXECUTE IMMEDIATE` raise Error, I should log that error and continue to next formula in table. But unfortunately, that should not happen in my case, I should omit error and apply formula to other rows in dynamic SQL, and in my case return NULL instead of Divide by zero, as Mr @Linoff suggest is sufficient. Thank you! — Pham X. Bach, Jul 31 '21 at 03:00

score 2 · Answer 1 · answered Jul 31 '21 at 02:22

2

You can use NULLIF() to prevent an error. The result will be NULL instead:

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

answered Jul 31 '21 at 02:22

Gordon Linoff

1,242,037
58
646
786

Thanh you for your idea, But formula is data in a table, and I cannot manually edit them like that. I will try using some `REGEXP_REPLACE` when SELECT to change those formulas using `NULLIF` – Pham X. Bach Jul 31 '21 at 02:47

Pham X. Bach · Answer 2 · 2021-08-01T04:27:38.133

Thanks to Mr @Linoff's suggestion, I do use `` to fetch and convert formula using NULLIF like this

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,
    REGEXP_REPLACE(REPLACE(formula, ' ', ''), '\/(n\d+)|\/(\([^\)]*\))', '/NULLIF(\1\2,0)') AS formula1
FROM tmp;

Result:

formula                     formula1                                    
n5/n14+n3                   n5/NULLIF(n14,0)+n3                                     
n5/n14+n3/n1-n2             n5/NULLIF(n14,0)+n3/NULLIF(n1,0)-n2                     
n8/(n17*n6)-n5/n4+n3/n1-n2  n8/NULLIF((n17*n6),0)-n5/NULLIF(n4,0)+n3/NULLIF(n1,0)-n2
n5/(n14+n3*n2)-n1           n5/NULLIF((n14+n3*n2),0)-n1

This regex will not handle multiple brackets like `a1/(a2+a3) + (a4+a4)/a5`. Things get even worse when there are nested brackets which cannot be handled with simple `\([^\(]+\)`. I think this task is better to address to PL/SQL with processing via stack or recursive calls — astentx, Jul 31 '21 at 07:43
@astentx You're right. I can only change regex a little bit to handle simple muliple brackets. But for nested brackets, after searching it's hard to find corresponding close bracket using regex. — Pham X. Bach, Aug 01 '21 at 04:30

score 1 · Answer 3 · answered Jul 31 '21 at 09:47

Write a parser in Java to parse your expression (or use an existing parser).
Write a static Java function to convert the abstract syntax tree generated by the parser to your required output and wrap each denominator in the division with a NULLIF or CASE expression to prevent division-by-zero exceptions being raised.
Load it into the database using the loadjava utility.
Write a PL/SQL function to wrap the Java function.
Use the PL/SQL function to transform your dynamic code into something safer.

Oracle Create formula for dynamic SQL to prevent divide by zero

3 Answers3