Oracle update issue with AND operator

Question

I have a staging table in oracle, all columns are set to varchar2 to accept text data from excel import functionality. Once the data is bulk copied to staging table, I validate the data & flag errors for records which failed validation.

One of my validation statement throws an error of "Invalid Number". Is there any way to stop oracle from evaluating the right side of AND condition when left side has failed?

My update statement is as below & the AMT columns has text non-numeric data.

UPDATE StagingTable
SET ErrorTXT = Error_txt || 'Invalid Amount, '
WHERE f_isnumber (INV_AMT) > 0
AND TO_NUMBER (INV_AMT) > 9999999999.99;

INV_AMT column as junk non numeric data. I am expecting the left side of the condition which evaluates to false, for non-numeric data, should stop evaluation of right side & thus Invalid Number error should not be thrown. Is there any hint I can use?

Either do two UPDATE, or use a case expression in the WHERE clause. — jarlh, Jan 30 '20 at 09:51
possible duplicate of: https://stackoverflow.com/questions/4486949/safe-to-number — cosmos, Jan 30 '20 at 09:55

score 0 · Answer 1 · answered Jan 30 '20 at 09:52

0

You can use ON CONVERSION ERROR with TO_NUMBER if you are using Oracle 12.2 or higher as following so that you dont need f_isnumber function:

UPDATE StagingTable
SET ErrorTXT = Error_txt || 'Invalid Amount, '
WHERE TO_NUMBER(INV_AMT DEFAULT -1 ON CONVERSION ERROR)  > 9999999999.99;

Cheers!!

answered Jan 30 '20 at 09:52

Popeye

35,427
4
10
31

This looks a good option. My version of oracle showing 12.2. But the above statement showing syntax issues on DEFAULT. – Jay Jan 30 '20 at 09:58
[Error] ORA-43907 (195: 47): PL/SQL: ORA-43907: This argument must be a literal or bind variable. This is the error I see. I am able to use that in select statement though. – Jay Jan 30 '20 at 10:00
Show me the exact code where you are facing this error. I think you must have used different value or function in default clause – Popeye Jan 30 '20 at 10:07
I am able to run the statement. I guess its throwing that error only during compiling package. – Jay Jan 30 '20 at 10:15

score 0 · Answer 2 · answered Jan 30 '20 at 12:17

You can also use a case expression. Assuming the function f_isnumber() does this test:

WHERE (CASE WHEN f_isnumber(INV_AMT) > 0 THEN TO_NUMBER(INV_AMT)
       END) > 9999999999.99;

If not, you can use a regular expression:

WHERE (CASE WHEN REGEXP_LIKE(INV_AMT, '^-?[0-9]*([.][0-9])?[0-9]*') 
            THEN TO_NUMBER(INV_AMT)
       END) > 9999999999.99;

Oracle update issue with AND operator

2 Answers2