Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pl/sql - commas, thousand separator

Posted on 2007-03-21
3
Medium Priority
?
4,144 Views
Last Modified: 2013-12-19
Good morning,

Is there a way to format the results of a query so that commas (thousand separators) show up in the results.
 
For example in Visual basic you can use the format function but can I do this using PL/SQL so that no additional formatting would need to be done in VB?

Here is my query:
Select Null as Date1, Disputed, OverdueUnknown, Round((Disputed * 263 / 201407169.04),2) as DDSO,Round((OverdueUnknown * 263 / 201407169.04),2) As OUDSO From (SELECT Sum(DECODE(GPRECL.PROBLEM_ID, 0, 0, GPRECL.BALANCE)) AS Disputed, Sum(DECODE( GPRECL.PROBLEM_ID, 0, GPRECL.BALANCE, 0)) AS OverdueUnknown FROM GPCOMP10.GPRECL GPRECL  WHERE GPRECL.BALANCE > 0 AND GPRECL.DUEDATE <= TO_DATE(SYSDATE))
0
Comment
Question by:lorincha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18764398
sample usage of to_char:
select to_char(234.2342340, '999,999,990.000') from dual    

in your case:
Select Null as Date1, Disputed, OverdueUnknown
, to_char(Round((Disputed * 263 / 201407169.04),2) , '999,999,999,990.00') as DDSO
, to_char(Round((OverdueUnknown * 263 / 201407169.04),2) , '999,999,999,990.00') As OUDSO From (SELECT Sum(DECODE(GPRECL.PROBLEM_ID, 0, 0, GPRECL.BALANCE)) AS Disputed, Sum(DECODE( GPRECL.PROBLEM_ID, 0, GPRECL.BALANCE, 0)) AS OverdueUnknown FROM GPCOMP10.GPRECL GPRECL  WHERE GPRECL.BALANCE > 0 AND GPRECL.DUEDATE <= TO_DATE(SYSDATE))
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18764407
note:
TO_DATE(SYSDATE))
is nonsense. SYSDATE IS already date, so remove that call to TO_DATE.
if you want to get the date part only, use the TRUNC() function instead
0
 

Author Comment

by:lorincha
ID: 18764561
Hi Angellll,

If you don't mind me asking....

TO_CHAR(OverDueUnknown, '999,999.99')

Say for instance that the number grows to the billions, in that case if I used the example above the results shows ######## in the result set.

Do you just need to put as many 999s as possible to predict the largest # possible?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question