CODEWITHSUNDEEP
oracle-databaseshelldecode
Angelina
Angelina

Reputation: 2265

Need number returned to be an integer instead of float, error: integer expression expected

In my database I have numbers stored with many trailing decimals ie.:

-99.00000940061668045423779799954877039

PRNCT_CHANGE    NUMBER

`

When I try to call this column and get the value stored into shell variable i.e.:

get_count () {
    sqlplus -s user/pass <<!
    set heading off
    set feedback off
    set pages 0
    select PRNCT_CHANGE
    FROM SEMANTIC.COUNT_STATISTICS
    ;
!
}

count=$(get_count $1)

It returns :

line 72: [:   -99.000009: integer expression expected

I am trying so hard to figure out how to fix this. I don't know how to get rid of all of these decimal spaces...

update SEMANTIC.COUNT_STATISTICS
set prnct_change =
(
   DECODE(OLD_COUNT, 0, NULL, ((NEW_COUNT-OLD_COUNT)/OLD_COUNT*100))
)

If anyone knows how please help.

Is there a way to format the number within UPDATE statement?

Upvotes: 0

Views: 335

Answers (1)

Ben
Ben

Reputation: 52893

Are the decimal places not useful for other things? I wouldn't destroy data just because you don't need it at the moment; you should transform it when you take it out of the database:

select trunc(prnct_change) from count_statistics

The default behavior of TRUNC() on a number is to remove all decimal places.

Upvotes: 2

Related Questions