CODEWITHSUNDEEP
oracle-databasedecimaloracle-sqldeveloper
Maik_h821
Maik_h821

Reputation: 9

Validating decimals in a column - oracle sqldeveloper

Im trying to come up with a test that validates decimals in a particular column (with 220000 records). For example for column A there shouldn't be any values with more decimals than 2, 1 is also ok.

for example :

Column A (datatype varchar)
48528.64
135082.54
5249.1

I tried with round function but than I get an error saying invalid number.

Also I would like to be able to change the number of decimals I put in the test to use with different columns

For example Its 1 big table with all columns having datatype VARCHAR2(2000 char)

examples for columns:

total amount (value should have no more than 2 decimals)
48528.64
135082.54
349.1123 (not OK)


Balance  (value should have no more than 2 decimals)
45428.64
1895082.11
5249.1483 (not OK)              


Loan  (value should have no more than 6 decimals)
100.64
88999.11654
1000.178875554 (not OK)

For each column I want to set up a seperate test that checks if the value is within the number of decimals allowed. So preferable a select statement with a where clause where I can adjust the numbers of decimals so I end up with all records having 1 or 2 decimals, or all the records that have more than 2 decimals

Upvotes: 1

Views: 897

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Invalid number error is due to the fact that you have something that isn't a number in that column, so when you apply numeric function to it, Oracle complains. That's what you get when you store numbers as strings. Don't do that.

Anyway, here's one option which shows what you might try to do: as these are strings, calculate number of digits right of the decimal point.

SQL> select * From test;

A
--------------------
48528.64        -- OK
135082.54       -- OK
5249.1          -- OK
1.2345          -- not OK
-25.553         -- not OK

SQL> select *
  2  from test
  3  where length(regexp_substr(a, '\d+$')) > 2;

A
--------------------
1.2345
-25.553

SQL>

If there are several columns and you'd like to check each of them using a separate table which holds allowed number of decimals, then you could do something like this:

SQL> with
  2  big (total, balance, loan) as
  3    (select  48528.64   ,   45428.64  ,   100.64        from dual union all
  4     select 135082.54   , 1895082.11  , 88999.11654     from dual union all
  5     select    349.1123 ,    5249.1483,  1000.178875554 from dual
  6    ),
  7  septest (tdec, bdec, ldec) as
  8    (select 2, 2, 6 from dual)
  9  select
 10    b.total,
 11    case when length(regexp_substr(b.total,'\d+$')) > s.tdec then 'Not OK'
 12         else 'OK'
 13    end total_ok,
 14    --
 15    b.balance,
 16    case when length(regexp_substr(b.balance,'\d+$')) > s.bdec then 'Not OK'
 17         else 'OK'
 18    end balance_ok,
 19    --
 20    b.loan,
 21    case when length(regexp_substr(b.loan,'\d+$')) > s.ldec then 'Not OK'
 22         else 'OK'
 23    end loan_ok
 24  from big b cross join septest s;

     TOTAL TOTAL_OK      BALANCE BALANCE_OK       LOAN LOAN_OK
---------- ---------- ---------- ---------- ---------- ----------
  48528,64 OK           45428,64 OK             100,64 OK
 135082,54 OK         1895082,11 OK         88999,1165 OK
  349,1123 Not OK      5249,1483 Not OK     1000,17888 Not OK

SQL>

Lines #1 - 8 represent sample data; you already have that. Query you actually need begins at line #9.

Upvotes: 1

Related Questions