Home » SQL & PL/SQL » SQL & PL/SQL » decimal problem
decimal problem Tue, 29 January 2008 02:59
 ss_ss Messages: 12Registered: November 2007 Junior Member
i've written a function to place a decimal into an integer at a specified position.

Now in my query im applying the above mentioned function individually and then multiplying those two fields but not getting the decimal point at the desired position.The datatype of both the fields are same i.e. number.

Example:

```SELECT CASE
WHEN(SUBSTR(a.salary1, -2) != ' ' OR SUBSTR (a.salary1, -2) IS NOT NULL)
THEN SUBSTR(a.salary1,1,LENGTH (a.salary1) -2) || '.'|| SUBSTR (a.salary1, -2)
ELSE ('0.0' || a.salary1)
END *
CASE WHEN (SUBSTR (b.salary2, -5) != ' ' OR SUBSTR (b.salary2, -5) IS NOT NULL)
THEN SUBSTR(b.salary2,1,LENGTH (b.salary2) - 5) || '.' || SUBSTR (b.salary2, -5)
ELSE ('.0' || b.salary2)
END  "Total Salary"
FROM table_a a, table_b b```

[Updated on: Tue, 29 January 2008 03:20] by Moderator

Report message to a moderator

Re: decimal problem [message #296819 is a reply to message #296818] Tue, 29 January 2008 03:09
 mm_kanish05 Messages: 488Registered: January 2007 Location: Chennai Senior Member
hi

wbr
kanish
Re: decimal problem [message #296823 is a reply to message #296818] Tue, 29 January 2008 03:15
 dhananjay Messages: 635Registered: March 2002 Location: Mumbai Senior Member
why don't you use the TO_CHAR() instead?

regards,
Re: decimal problem [message #296824 is a reply to message #296823] Tue, 29 January 2008 03:20
 ss_ss Messages: 12Registered: November 2007 Junior Member
Tried, not working.
Re: decimal problem [message #296828 is a reply to message #296818] Tue, 29 January 2008 03:31
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Senior Member
What did you try so far with to_char ?

If you are trying to display just One field no need for Case-End keywords . merge the together.(But you can do all these with TO_CHAR use.

There is logical issue in your query also . As long as SUBSTR (a.salary1, -2) is NOT NULL Only first case expression will be displayed .

Rajuvan.

[Updated on: Tue, 29 January 2008 03:34]

Report message to a moderator

Re: decimal problem [message #296830 is a reply to message #296824] Tue, 29 January 2008 03:32
 dhananjay Messages: 635Registered: March 2002 Location: Mumbai Senior Member
can you post it.

```select ename,
sal,to_char(sal,'9999.99')format_sal
from emp
/

ENAME      SAL                    FORMAT_SAL
---------- ---------------------- ----------
SMITH      800                      800.00
ALLEN      1600                    1600.00
WARD       1250                    1250.00
JONES      2975                    2975.00
MARTIN     1250                    1250.00
BLAKE      2850                    2850.00
CLARK      2450                    2450.00
SCOTT      3000                    3000.00
TURNER     1500                    1500.00
JAMES      950                      950.00
FORD       3000                    3000.00
MILLER     1300                    1300.00
KING       5000                    5000.00

```

regards,
Re: decimal problem [message #296835 is a reply to message #296830] Tue, 29 January 2008 03:49
 ss_ss Messages: 12Registered: November 2007 Junior Member
Im multiplying two values.

thanks all i got the solution, which is as follows

SELECT ts.salary1, er.salary2, TO_CHAR((ts.salary1/100000)*(er.salary2/100000), '999999999.99') res FROM emp1 ts,emp2 er
 Previous Topic: Hierarchical Query question Next Topic: function & procedure
Goto Forum:

Current Time: Tue Aug 22 19:39:12 CDT 2017

Total time taken to generate the page: 0.05477 seconds