Home » SQL & PL/SQL » SQL & PL/SQL » Negative value in braces  () 1 Vote
Negative value in braces [message #195331] Thu, 28 September 2006 01:36 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I am getting some negative values from a column like -300,400,-200.
How can I change the display as below:
(300),400,(200).

I have to produce a report and want the negative values to come in small braces.
Is there any formatting formula for this?

Thanks,
Mona

[Updated on: Thu, 28 September 2006 01:36]

Report message to a moderator

Re: Negative value in braces [message #195335 is a reply to message #195331] Thu, 28 September 2006 01:48 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

select thevalue,decode(sign(thevalue),1,to_char(thevalue),'('||to_char(abs(thevalue))||')')thevalue from (select -200 thevalue from dual
union all
select 100 from dual
union all
select -400 from dual
union all
select -500 from dual
union all 
select 250 from dual)


regards,
Re: Negative value in braces [message #195336 is a reply to message #195335] Thu, 28 September 2006 01:54 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

There is a lot of data. I can't use this way. Isn't there any other way which is more generalised one?

Thanks,
Mona
Re: Negative value in braces [message #195342 is a reply to message #195336] Thu, 28 September 2006 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You did notice that all the UNION statements in that query were just to provide the data for the query to use, and are not a vital part of the solution?

Just create a function to do the conversion for you and it'll all look neat and tidy:

CREATE OR REPLACE FUNCTION ACCOUNTING_FORMAT (p_number  in  number)
RETURN VARCHAR2 AS
  v_return  varchar2(50);
BEGIN
  v_return := to_char(p_number);
  If sign(p_number) = -1 THEN
    v_return := '('||p_number||')';
  end if;
  return v_return;
END;
/

SQL> select thevalue
  2        ,accounting_format(thevalue)
  3  from (select -200 thevalue from dual
  4  union all
  5  select 100 from dual
  6  union all
  7  select -400 from dual
  8  union all
  9  select 0 from dual
 10  union all 
 11  select 250 from dual);

  THEVALUE  ACCOUNTING_FORMAT(THEVALUE)
  ----------------------------------------------
      -200  (-200)
       100  100
      -400  (-400)
         0  0
       250  250




Re: Negative value in braces [message #195343 is a reply to message #195342] Thu, 28 September 2006 02:21 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks JRowBottom.

Regards,
Mona
Re: Negative value in braces [message #195370 is a reply to message #195331] Thu, 28 September 2006 05:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from test_neg;

	A1
----------
      -300
       400
      -200

SQL> select translate(to_char(a1,'9999PR'),'<>','()') from test_neg;

TRANSL
------
 (300)
  400
 (200)
icon10.gif  Re: Negative value in braces [message #195502 is a reply to message #195331] Fri, 29 September 2006 01:43 Go to previous message
vijayanarayanan
Messages: 11
Registered: September 2006
Location: chennai
Junior Member
YOU ARE getting some negative values from a column like -300,400,-200.
TO GET the display as below:
(300),400,(200).


GIVE THIS SOLUTION:

SELECT CASE WHEN INSTR(A,'-')=1 THEN '('||SUBSTR(A,INSTR(A,'-')+1)||')'
ELSE TO_CHAR(A) END
FROM SIG;



Previous Topic: Spooling my files so sql is not included in output file
Next Topic: Oracle and C integration...............
Goto Forum:
  


Current Time: Sun Dec 04 14:56:52 CST 2016

Total time taken to generate the page: 0.11507 seconds