Home » SQL & PL/SQL » SQL & PL/SQL » Seperating the single column values into two columns
icon7.gif  Seperating the single column values into two columns [message #261588] Thu, 23 August 2007 01:47 Go to next message
marisa
Messages: 4
Registered: April 2007
Junior Member
hi,

I have requirement in which i have temp column consisting of both +ve and -ve values.

i have to show +ve & -ve values in seperate columns using SQL query.

please assist me in this regard
Re: Seperating the single column values into two columns [message #261591 is a reply to message #261588] Thu, 23 August 2007 01:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A DECODE could separate positive and negative:
SELECT DECODE( SIGN(theval)
             , 1, theval
             , NULL
             ) pos
     , DECODE( SIGN(theval)
             , 1, TO_NUMBER(NULL)
             , theval
             ) neg
FROM   yourtable
/


MHE
Re: Seperating the single column values into two columns [message #261605 is a reply to message #261591] Thu, 23 August 2007 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why 0 is negative? Cool

Regards
Michel
Re: Seperating the single column values into two columns [message #261717 is a reply to message #261605] Thu, 23 August 2007 07:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://mathforum.org/library/drmath/view/58735.html and the answer is ?????

[Updated on: Thu, 23 August 2007 07:33]

Report message to a moderator

Re: Seperating the single column values into two columns [message #262410 is a reply to message #261588] Mon, 27 August 2007 00:01 Go to previous messageGo to next message
sqlc
Messages: 6
Registered: August 2007
Junior Member
hi marisa

select count(decode(sign(sihn),1,sihn,0,sihn)) positive,count(decode(sign(sihn),-1,-1*sihn) ) negative,count(*) total from sihn;


i guess this works
Re: Seperating the single column values into two columns [message #262438 is a reply to message #262410] Mon, 27 August 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i guess this works

And 0?

Regards
Michel
Re: Seperating the single column values into two columns [message #262453 is a reply to message #262438] Mon, 27 August 2007 01:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Mon, 27 August 2007 07:58
And 0?
Especially for you, Michel:
SQL> WITH yourtable AS
  2  (
  3    SELECT -3 theval FROM dual UNION ALL
  4    SELECT  2 theval FROM dual UNION ALL
  5    SELECT -5 theval FROM dual UNION ALL
  6    SELECT  4 theval FROM dual UNION ALL
  7    SELECT  2 theval FROM dual UNION ALL
  8    SELECT  0 theval FROM dual UNION ALL
  9    SELECT  7 theval FROM dual UNION ALL
 10    SELECT -2 theval FROM dual UNION ALL
 11    SELECT -4 theval FROM dual UNION ALL
 12    SELECT  0 theval FROM dual
 13  )
 14  SELECT DECODE( SIGN(theval)
 15               , 1, theval
 16               , NULL
 17               ) pos
 18       , DECODE( SIGN(theval)
 19               , -1, theval
 20               , NULL
 21               ) neg
 22      ,  DECODE( SIGN(theval)
 23               , 0, theval
 24               , NULL
 25               ) zero
 26  FROM   yourtable
 27  /

       POS        NEG       ZERO
---------- ---------- ----------
                   -3
         2
                   -5
         4
         2
                               0
         7
                   -2
                   -4
                               0

10 rows selected.


MHE
Re: Seperating the single column values into two columns [message #262458 is a reply to message #262453] Mon, 27 August 2007 02:03 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, every one is missing it but 0 is pretty, it is small, it is weak but like the water, no one can fight against it.
Don't forget 0.

Regards
Michel
Previous Topic: day, week, month and year grouping
Next Topic: Replace and underscores
Goto Forum:
  


Current Time: Fri Dec 09 00:14:20 CST 2016

Total time taken to generate the page: 0.13420 seconds