Trailing 0 got dropped

when you do math on the address field, the result is implicitly converted to a NUMBER type which will display in simplest form.

if you want formatted results, then you need to return the math result as text


select NAME,ADDRESS,to_char(ADDRESS-7,'999999.00') from entity where ....

Put

col res format 999,999.00
select NAME,ADDRESS,ADDRESS-7 res from entity where ....

Select allOpen in new window

Thanks for the prompt reply:
@sdstuber: The results is all double decimal points
154.10    147.10
154.1      147.10
154.15    147.15
155.0      148.00

Which is not the result i'm looking for.

@HuaMinChen: There is a SQL format error in the statment.

Just to clarify the results i would like to achieve is:

154.10    147.10
154.1      147.1
154.15    147.15
155.0      148.0

Do you put "res" as the column name? what is the error?

@HuaMinChen: the error is Invalid SQL Statement.

I ran the exact same statement:
col res format 999,999.00
select NAME,ADDRESS,ADDRESS-7 res from entity where ....

and tried this also witht the same error
col res format 999,999.00
select NAME,ADDRESS,ADDRESS-7 as "res" from entity where ....

Try

col res format 999,999.00
select NAME,ADDRESS,ADDRESS-7 as res from entity where ....

Select allOpen in new window

Not to put "res"

the "col" syntax is for sqlplus

Toad can mimic the results but only when running as script.

to get the formatted results in the data grid, you'll need to use to_char in the query

to get the same number of decimals as the original text  try this...

SELECT name,
       address,
       CASE
           WHEN INSTR(address, '.') > 0
           THEN
               TO_CHAR(
                   address - 7,
                   'fm9999.' || RPAD('0', LENGTH(SUBSTR(address, INSTR(address, '.') + 1)), '0')
               )
           ELSE
               TO_CHAR(address - 7)
       END x


if there will always be a decimal point, then it can be simplified by removing the CASE


SELECT name,
       address,
       TO_CHAR(
           address - 7,
           'fm9999.' || RPAD('0', LENGTH(SUBSTR(address, INSTR(address, '.') + 1)), '0')
       ) x
  FROM entity


  FROM entity

Similarly with explicit conversions -
select address,
case when length(address) - instr(address,'.') = 2 then to_char(to_number(address) - 7,'9999999.00')
     when length(address) - instr(address,'.') = 1 then to_char(to_number(address) - 7,'9999999.0')
     else to_char(to_number(address) - 7,'9999999')
end addminus7
from entity;

Can it not be simplified a little?

I'm thinking the address column must be varchar2 or it wouldn't have the trailing '0' to start with.

Just subtract 7 from the whole number and tack on everything after the decimal?


select floor(to_number(address))-7 || regexp_substr(address,'\..+$') from entity;