Home » SQL & PL/SQL » SQL & PL/SQL » how is that result???
how is that result??? [message #210590] Thu, 21 December 2006 07:52 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii all

i have this query ordinary query
select to_char(333.31,'999.999') from dual;


which result 333.310

and till here there is no problem
now i used the first part "to_char(333.31,'999.999')
in another query

select decode (substr (to_char(333.300,'99999.999'),7,4),'.000'
,333,[B]to_char(333.31,'999.999')[/B]) qty
from dual;


the result is 333.31

why the result is like that not 333.310
like the first query


Thanks for everyone helped and helping me
Re: how is that result??? [message #210593 is a reply to message #210590] Thu, 21 December 2006 07:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Because the first possible returnvalue from your decode is a number, Oracle assumes you are going to return a number for the second part as well.
Because it is a number, the trailing zero is not displayed.
If you change the first option (333) to a string ('333') you will see it returns 333.310 again.
Re: how is that result??? [message #210652 is a reply to message #210593] Thu, 21 December 2006 12:16 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Additionally, I think you can just make the format mask '99999.990' instead of '99999.999'.

However, then I run your query, I get nothing returned.

  1  select decode (substr (to_char(333.300,'99999.999'),7,4),'.000' ,333,333.31,'999.999')  qty
  2* from dual
foobar SCOTT> /

       QTY
----------


Re: how is that result??? [message #210710 is a reply to message #210652] Thu, 21 December 2006 22:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@joy: you miss a to_char; now you added extra options to the decode.
Re: how is that result??? [message #210823 is a reply to message #210710] Fri, 22 December 2006 08:43 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Wow, that's weird, I thought I just cut and pasted, then removed the tags. It's not been a good week for me.
Re: how is that result??? [message #210834 is a reply to message #210823] Fri, 22 December 2006 10:36 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Well, now everyone can see the benefits of SHOWING what you tried, instead of just claiming that something went wrong Smile
Previous Topic: What is max limit of oracle storage
Next Topic: How to replace all single quote by another character
Goto Forum:
  


Current Time: Fri Dec 02 20:48:52 CST 2016

Total time taken to generate the page: 0.13502 seconds