Home » SQL & PL/SQL » SQL & PL/SQL » decimal problem
decimal problem [message #296818] Tue, 29 January 2008 02:59 Go to next message
ss_ss
Messages: 12
Registered: 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 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

Read 1st How to post your topics

wbr
kanish
Re: decimal problem [message #296823 is a reply to message #296818] Tue, 29 January 2008 03:15 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: 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 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
Tried, not working.
Re: decimal problem [message #296828 is a reply to message #296818] Tue, 29 January 2008 03:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: 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 .

Thumbs Up
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 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: 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   
ADAMS      1100                    1100.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 Go to previous message
ss_ss
Messages: 12
Registered: 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: ascii to char conversion....
Next Topic: Select statement for export with timestamp
Goto Forum:
  


Current Time: Wed Dec 07 18:17:02 CST 2016

Total time taken to generate the page: 0.29021 seconds