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 Go to next message
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 #337826 is a reply to message #337822] Fri, 01 August 2008 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

With your formatting we don't know which columns are filled and which ones are not.
Now, how do you compare? more what compare means for you?

Quote:
i have tried writin few queries but unable to succees.

Post them and explain why it does not work, maybe we will understand what you want.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: comparing values in a table [message #337969 is a reply to message #337822] Fri, 01 August 2008 15:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
coalesce:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions023.htm#SQLRF00617
Re: comparing values in a table [message #338041 is a reply to message #337822] Sat, 02 August 2008 13:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Barbara, help me out here, what is this guy even asking?

Kevin
Re: comparing values in a table [message #338042 is a reply to message #338041] Sat, 02 August 2008 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Who knows? Does OP know? Wink
I think I understood the same thing than Barbara (as I thought about the same solution) but is it the real problem? At least it is one solution for one possible problem. Laughing

Regards
Michel
Re: comparing values in a table [message #338049 is a reply to message #338042] Sat, 02 August 2008 15:11 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: error converting datatype varchar to float
Next Topic: Copying a file from local drive into server
Goto Forum:
  


Current Time: Tue Feb 11 05:26:46 CST 2025