Home » SQL & PL/SQL » SQL & PL/SQL » negative value
negative value [message #189874] Mon, 28 August 2006 04:33 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
Is there any way to produce a column value so that it can be sum to zero.

like
xyz table
col1 col2
a    -800
b    -200
c    700

so that how to create another line with counter value so that summaztion at any stage should be zero

ie

xyz table

col1   col2
a      -800
b      -200
c      700
a     800
b     200
c     -700

I donot want to use - sign so get counter value, is there any way to create these lines
Re: negative value [message #189875 is a reply to message #189874] Mon, 28 August 2006 04:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here are 2 options:

The script:
CREATE TABLE yourtable(col1 VARCHAR2(10), col2 NUMBER)
/

INSERT INTO yourtable VALUES ('a',  -800);
INSERT INTO yourtable VALUES ('b',  -200);
INSERT INTO yourtable VALUES ('c',  700 );

Prompt option 1: select twice
SELECT col1
     , col2
FROM   yourtable
UNION ALL
SELECT col1
     , -1*col2 col2
FROM   yourtable
/

Prompt option 2: use a row generator
SELECT t.col1
     , DECODE(v.l,1,t.col2,-1*t.col2) col2
FROM   yourtable t
   ,   ( SELECT LEVEL l
         FROM   dual
         CONNECT BY LEVEL <= 2
       ) v
/

DROP TABLE yourtable
/


The run:
SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.

option 1: select twice

COL1             COL2
---------- ----------
a                -800
b                -200
c                 700
a                 800
b                 200
c                -700

6 rows selected.

option 2: use a row generat

COL1             COL2
---------- ----------
a                -800
b                -200
c                 700
a                 800
b                 200
c                -700

6 rows selected.


Table dropped.

SQL>


MHE
Re: negative value [message #189877 is a reply to message #189875] Mon, 28 August 2006 05:03 Go to previous message
sanjit
Messages: 65
Registered: November 2001
Member
Thanks a lot

Just a small input.

Now can it be inserted a row based out of the group of col1

ie
COL1             COL2
---------- ----------
a                -800
b                -200
c                 700
a                 800
b                 200
c                -700



another

COL1             COL2
---------- ----------
a                -800
b                -200
c                 700
a                 800
b                 200
c                -700
a                 0
b                 0
c                 0


Is by Connect
if insert based out of group of col1
Previous Topic: Tree Level Queries
Next Topic: UTL_FILE - Invalid_Operation
Goto Forum:
  


Current Time: Fri Dec 09 23:21:11 CST 2016

Total time taken to generate the page: 0.13349 seconds