Home » SQL & PL/SQL » SQL & PL/SQL » how to place a decimal point into a number at a desired position
how to place a decimal point into a number at a desired position [message #284520] Thu, 29 November 2007 23:43 Go to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
i want to place the decimal point into a number at a desired position, so how it can be achieved?
Re: how to place a decimal point into a number at a desired position [message #284522 is a reply to message #284520] Thu, 29 November 2007 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>i want to place the decimal point into a number at a desired position, so how it can be achieved?
It can be achieved by counting; but it matters whether you count from the right or left.
Re: how to place a decimal point into a number at a desired position [message #284526 is a reply to message #284522] Fri, 30 November 2007 00:05 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
i want 2 digits after the decimal i.e. count from right.
Re: how to place a decimal point into a number at a desired position [message #284527 is a reply to message #284520] Fri, 30 November 2007 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the posting guidelines.

We have no idea about ANYTHING in your environment.
Operating system name & version.
Oracle version to 4 decimal places.
Which client you are using?

Re: how to place a decimal point into a number at a desired position [message #284528 is a reply to message #284520] Fri, 30 November 2007 00:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Use Round or Trunc Function as per your requirement

Thumbs Up
Rajuvan.

Re: how to place a decimal point into a number at a desired position [message #284532 is a reply to message #284528] Fri, 30 November 2007 00:25 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
those two are not working as i want to place the decimal point into an integer.

orcale version 9.2.0.6.0 64bit
windows xp
ora client
Re: how to place a decimal point into a number at a desired position [message #284536 is a reply to message #284532] Fri, 30 November 2007 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Divide by 100.

Regards
Michel
Re: how to place a decimal point into a number at a desired position [message #284537 is a reply to message #284520] Fri, 30 November 2007 00:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>ora client
I have no idea what this means or is.
TOAD, SQL*Plus, Perl are all Oracle clients; plus many others.
Re: how to place a decimal point into a number at a desired position [message #284553 is a reply to message #284536] Fri, 30 November 2007 01:09 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
division thing is fine but i want to knw if therez is any function to do so.

client-TOAD
Re: how to place a decimal point into a number at a desired position [message #284554 is a reply to message #284553] Fri, 30 November 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

division thing is fine but i want to knw if therez is any function to do so.

/

Regards
Michel
Re: how to place a decimal point into a number at a desired position [message #284557 is a reply to message #284520] Fri, 30 November 2007 01:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


10 Postings are over. Still not clear on your requirement .
How did you try with round and trunc ?
If division is fine why do you one function for that ?

Tell us what exactly your need with examples (Copy ,Paste)
and with the queries you experimented
and with the reason why you are satified with the your queries so far.

Thumbs Up
Rajuvan
Re: how to place a decimal point into a number at a desired position [message #284567 is a reply to message #284554] Fri, 30 November 2007 01:38 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
SELECT PLMN_ID_REMOTE
     , SDR_VALUE
     , SDR_TAX
     , USD_VALUE
     , USD_TAX
     , SDR_DISCOUNT
     , (SDR_VALUE + SDR_TAX - SDR_DISCOUNT) "NET"
FROM   (SELECT   TS.PLMN_ID_REMOTE "PLMN_ID_REMOTE"
               , SUM(TA.TAP_VALUE_TOTAL) "SDR_VALUE"
               , SUM(TS.AUDIT_TOTAL_TAX) "SDR_TAX"
               , SUM(TA.TAP_VALUE_TOTAL * ER.EXCHANGE_RATE) "USD_VALUE"
               , SUM(TS.AUDIT_TOTAL_TAX * ER.EXCHANGE_RATE) "USD_TAX"
               , NVL(DD1.DISCOUNT, 0) "SDR_DISCOUNT"
        FROM     APPADM.TAP_STATS TS
               , (SELECT   FILE_ID
                         , SUM(TAP_VALUE) "TAP_VALUE_TOTAL"
                  FROM     APPADM.TAP_AMOUNTS
                  GROUP BY FILE_ID) TA
               , APPADM.EXCHANGE_RATES ER
               , (SELECT   DD.FILE_ID
                         , DD.DISCOUNT_RATE "DISCOUNT"
                  FROM     APPADM.DISCOUNT_DETAILS DD
                  WHERE    EXISTS(
                              SELECT FILE_ID
                              FROM   APPADM.TAP_STATS
                              WHERE  TO_CHAR(FILE_CREATION_DT, 'MON-yyyy') =
                                        (SELECT TO_CHAR(TO_DATE('NOV/2007'
                                                              , 'MON/yyyy'
                                                               )
                                                      , 'MON-yyyy'
                                                       )
                                         FROM   DUAL))
                  ORDER BY 1) DD1
               , (SELECT   TS.FILE_ID "FILE_ID"
                         , TS.PLMN_ID_REMOTE
                         , TS.BATCH_SEQ_NUM
                         , SUM(TETT.TOTAL) "TOTAL_EVENTS"
                  FROM     APPADM.TAP_EVENT_TYPE_TOTALS TETT
                         , APPADM.TAP_STATS TS
                  WHERE    EXISTS(
                              SELECT FILE_ID
                              FROM   APPADM.TAP_STATS
                              WHERE  TO_CHAR(FILE_CREATION_DT, 'MON-yyyy') =
                                        (SELECT TO_CHAR(TO_DATE('NOV/2007'
                                                              , 'MON/yyyy'
                                                               )
                                                      , 'MON-yyyy'
                                                       )
                                         FROM   DUAL))
                  AND      TS.FILE_ID = TETT.FILE_ID
                  GROUP BY TS.FILE_ID
                         , TS.PLMN_ID_REMOTE
                         , TETT.FILE_ID
                         , TS.BATCH_SEQ_NUM
                  ORDER BY 1) TE
        WHERE    TS.FILE_ID = TA.FILE_ID
        AND      TA.FILE_ID = ER.FILE_ID
        AND      TS.FILE_ID = TE.FILE_ID
        AND      TS.FILE_ID = DD1.FILE_ID(+)
        AND      TS.IN_OUT_INDICATOR = 0
        AND      EXISTS(
                    SELECT PLMN_ID_REMOTE
                    FROM   APPADM.TAP_STATS
                    WHERE  TO_CHAR(FILE_CREATION_DT, 'MON-yyyy') =
                              (SELECT TO_CHAR(TO_DATE('NOV/2007', 'MON-yyyy')
                                            , 'MON-yyyy'
                                             )
                               FROM   DUAL))
        GROUP BY TS.PLMN_ID_REMOTE, DD1.DISCOUNT
        ORDER BY 1);


this is my query

and below is its o/p:

PLMN_ID_REMOTE	SDR_VALUE  SDR_TAX	USD_VALUE	USD_TAX
BHRMV	        26916	    0	        26916000000	0	
VFNIN	        11913	    0	        11913000000	0	

SDR_DISCOUNT	NET
0	        26916
1500	        10413


nw i want to place the decimal after two digits from the right in all the fields except the plmn_id remote and dnt want to divide by 100 as performing some calculations and not the correct way.

round(SDR_VALUE,2) and trunc(SDR_VALUE,2)

[mod-edit] added [CODE] and [/CODE] tags. Do so yourself next time. It's a lot easier to read.

[Updated on: Fri, 30 November 2007 01:52] by Moderator

Report message to a moderator

Re: how to place a decimal point into a number at a desired position [message #284568 is a reply to message #284567] Fri, 30 November 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is just a display issue, use to_char function and format model.

Regards
Michel
Re: how to place a decimal point into a number at a desired position [message #284577 is a reply to message #284568] Fri, 30 November 2007 03:03 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
if i use to_char and format then it will be an issue as the number of digits increases according to that i need to format it.
Re: how to place a decimal point into a number at a desired position [message #284578 is a reply to message #284577] Fri, 30 November 2007 03:05 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Post sample data and expected output.
Do not post a list of values, post create table and insert scripts.
Re: how to place a decimal point into a number at a desired position [message #284579 is a reply to message #284520] Fri, 30 November 2007 03:07 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

http://www.orafaq.com/forum/m/284220/115564/#msg_284220

Kiran.
Previous Topic: order by clause
Next Topic: problem in removing data?
Goto Forum:
  


Current Time: Fri Dec 09 15:26:19 CST 2016

Total time taken to generate the page: 0.15899 seconds