Home » SQL & PL/SQL » SQL & PL/SQL » compare similar values (oracle 10)
compare similar values [message #400495] Tue, 28 April 2009 08:29 Go to next message
dior
Messages: 25
Registered: April 2009
Junior Member
Hi,

how can I compare similar values, not the same values?

e.g.

[U]Table 1:[/U]

   x1  |  y1   
154,100 053,530
154,101 053,586
154,200 053,576


[U]Table 2:[/U]

   x1  |  y1   
154,100 053,530
154,101 053,586
154,200 053,576


Result should be: X1 from table 1 with 154,100 and 154,101 as values.

select x1 from table 1 where table1.x1 ... table2.x2


the thing is, that i like to have all values simlar exept the last or the last 2 values!

thx
Re: compare similar values [message #400499 is a reply to message #400495] Tue, 28 April 2009 08:41 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use trunc and group with having.

By
Vamsi
Re: compare similar values [message #400500 is a reply to message #400495] Tue, 28 April 2009 08:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's getting a bit late here in Australia, and I'm 9/10th of my way through 3 fingers of Southern Comfort, but gee ... those two tables look the same to me.

Am I missing something obvious?

Assuming there is a difference between entries, you need to describe in ABSOLUTE TERMS what you mean by "similar".

For example, is 539 similar to 540 because they are numerically different by one? What about 540 and 340, which share two of the three same ASCII characters?

Describe situations where you WOULD expect a match, and situations where you wouldn't expect a match.

Ross Leishman
Re: compare similar values [message #400501 is a reply to message #400500] Tue, 28 April 2009 08:48 Go to previous messageGo to next message
dior
Messages: 25
Registered: April 2009
Junior Member
Quote:
Describe situations where you WOULD expect a match, and situations where you wouldn't expect a match.


thats what i meant with my last sentence:
the thing is, that i like to have all values simlar exept the last or the last 2 values!


in my table are a lot of coordinate values, but not every value is the same...

now i like to compare the values which are the same for maybe the last or the last 2 digits so that i can locate points within a few meters.
Re: compare similar values [message #400503 is a reply to message #400495] Tue, 28 April 2009 08:54 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Giving us sample data that showed that would have helped.

substr is what you probably need to use, and if you've got a lot of data you might want to consider some function based indexes to speed it up.
Re: compare similar values [message #400506 is a reply to message #400495] Tue, 28 April 2009 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: compare similar values [message #400549 is a reply to message #400495] Tue, 28 April 2009 12:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
dior wrote on Tue, 28 April 2009 09:29
H
[U]Table 1:[/U]

   x1  |  y1   
154,100 053,530
154,101 053,586
154,200 053,576


[U]Table 2:[/U]

   x1  |  y1   
154,100 053,530
154,101 053,586
154,200 053,576


Result should be: X1 from table 1 with 154,100 and 154,101 as values.

select x1 from table 1 where table1.x1 ... table2.x2


the thing is, that i like to have all values simlar exept the last or the last 2 values!



You may think you are explaining it will, but you sound like what Ross should sound like at this point in his drinking.

"except the last or the last 2 values" What the hell do you mean by this.You say values, but your semantics leave a lot to be desired. Is that row, column, value in row, etc? I see 2 values per column. Yet, then, you say last 2 digits. Please explain with the proper semantics what you mean. Better yet, show it, step by step.

And why do you expect those results? Why not 154, 200? Is this a Mensa puzzle or something?

[Updated on: Tue, 28 April 2009 12:55]

Report message to a moderator

Re: compare similar values [message #400564 is a reply to message #400549] Tue, 28 April 2009 16:21 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
When comparing two numbers to see if they's "close" in value to each other, you'd need ABS (not TRUNC, ROUND or SUBSTR). Similar approach for date values.
SQL> select abs(1999.994 - 2000.003) from dual;

ABS(1999.994-2000.003)
----------------------
                  .009

SQL> select abs(2000.003 - 1999.994) from dual;

ABS(2000.003-1999.994)
----------------------
                  .009

SQL>


For a rough formula, you could compare (abs(X delta) + abs(Y delta)) to your cutoff value, else use the proper formula... http://en.wikipedia.org/wiki/Distance


Previous Topic: How to load oracle table data into a file
Next Topic: partition user dictionary
Goto Forum:
  


Current Time: Sat Dec 03 22:30:38 CST 2016

Total time taken to generate the page: 0.08767 seconds