Home » SQL & PL/SQL » SQL & PL/SQL » comparing values in a table (Oracle 10G)
comparing values in a table [message #337822] |
Fri, 01 August 2008 05:14  |
saudalnasr
Messages: 7 Registered: August 2008
|
Junior Member |
|
|
hi gurus i have a table
jh_no jh_txn_code valorg R1 R2 R3 R4 R5
1 nwr 58 100 200 225 300 35
2 nwr 10 15
3 nwr 35 60 200 2558
4 nwr 12 50 119 300 444
5 nwr 5 9 300
i want to compare valorg column values with the R1,R2,R3,R4,R5 which ever is the last.
for eg row #
1) valorg - R5
2) valorg - R1
3) Valorg - R3
4) Valorg - R4
5) Varlog - R2.
i have tried writin few queries but unable to succees.
any help will be highly appreciated.
Saud
|
|
|
|
|
|
|
Re: comparing values in a table [message #338049 is a reply to message #338042] |
Sat, 02 August 2008 15:11   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
My best guess was something like the demo below. If it is not exactly what he wants, he should be able to modify it to get what he wants. I got the impression that the biggest issue was figuring out how to get the last r whatever value, for which coalesce is the most logical solution.
SCOTT@orcl_11g> CREATE TABLE a_table
2 (jh_no NUMBER,
3 jh_tx_code VARCHAR2 (3),
4 valorg NUMBER,
5 r1 NUMBER,
6 r2 NUMBER,
7 r3 NUMBER,
8 r4 NUMBER,
9 r5 NUMBER)
10 /
Table created.
SCOTT@orcl_11g> INSERT ALL
2 INTO a_table VALUES (1, 'nwr', 58, 100, 200, 225, 300, 35)
3 INTO a_table VALUES (2, 'nwr', 10, 15, NULL, NULL, NULL, NULL)
4 INTO a_table VALUES (3, 'nwr', 35, 60, 200, 2558, NULL, NULL)
5 INTO a_table VALUES (4, 'nwr', 12, 50, 119, 300, 444, NULL)
6 INTO a_table VALUES (5, 'nwr', 5, 9, 300, NULL, NULL, NULL)
7 SELECT * FROM DUAL
8 /
5 rows created.
SCOTT@orcl_11g> SELECT * FROM a_table
2 /
JH_NO JH_ VALORG R1 R2 R3 R4 R5
---------- --- ---------- ---------- ---------- ---------- ---------- ----------
1 nwr 58 100 200 225 300 35
2 nwr 10 15
3 nwr 35 60 200 2558
4 nwr 12 50 119 300 444
5 nwr 5 9 300
SCOTT@orcl_11g> SELECT jh_no,
2 valorg - COALESCE (r5, r4, r3, r2, r1, 0)
3 AS "diff b/t valorg and last r"
4 FROM a_table
5 /
JH_NO diff b/t valorg and last r
---------- --------------------------
1 23
2 -5
3 -2523
4 -432
5 -295
SCOTT@orcl_11g>
|
|
|
Re: comparing values in a table [message #338076 is a reply to message #338049] |
Sun, 03 August 2008 02:26  |
saudalnasr
Messages: 7 Registered: August 2008
|
Junior Member |
|
|
hello all,
sorry for replying little late on this , cause we had 3 days holiday.
guys this is the first time i am posting a issue to this forum as micheal rightly said i should have read how to post column before posting my issue.
my issue was rightly understood by barbera boehmer.
just for this i was using a cursor and it was taking a long time to fetch the result which i made in my holiday.
i didnt know about the function which barbara explained thanks a lot.
however i would like to thank all of u to help me out on this.
thanks
barbara, micheal, kevin.
|
|
|
Goto Forum:
Current Time: Tue Feb 11 05:26:46 CST 2025
|