Home » SQL & PL/SQL » SQL & PL/SQL » Row-wise comparison (Oracle 11i, Microsoft Windows XP)
Row-wise comparison [message #341902] Wed, 20 August 2008 13:43 Go to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
In Postgresql, you can create a statement such as the following:

SELECT 'hi' WHERE (a, b) < (c, d);

This will translate into the equivalent:

SELECT 'hi' WHERE a < c OR (a = c AND b < d);

Which is documented at the bottom of the following page:

http://www.postgresql.org/docs/8.2/static/functions-comparisons.html

I tried using this in Oracle and it complained about the syntax of the comparison. The actual query I used was:

SELECT 'hi' FROM dual WHERE (1, 2) < (1, 3);

The resulting error was: "This operator cannot be used with lists"

Is there a way to do this comparison in Oracle without having to unwind the corresponding ANDs and ORs?
Re: Row-wise comparison [message #341903 is a reply to message #341902] Wed, 20 August 2008 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Row-wise comparison [message #341905 is a reply to message #341902] Wed, 20 August 2008 13:49 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way to do this comparison in Oracle without having to unwind the corresponding ANDs and ORs?
No & what is the problem with unwinding the corresponding ANDs and ORs as a 1 time event.

By the way in 15+ years with Oracle I have never seen a real world application requiring such "logic'.

please provide a business scenario where such a construct would be valid.
Re: Row-wise comparison [message #341913 is a reply to message #341905] Wed, 20 August 2008 14:13 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Thanks for the prompt replies,

There's no critical reason not to unwind them. I was just curious as to whether oracle provided similar functionality.

The scenario is emulating some older sequential database.
Re: Row-wise comparison [message #341924 is a reply to message #341913] Wed, 20 August 2008 15:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You could lpad each individual column with zeroes (in case of number-columns) up to a fixed length and then concatenate the two
Re: Row-wise comparison [message #341929 is a reply to message #341913] Wed, 20 August 2008 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
psk1 wrote on Wed, 20 August 2008 12:13
Thanks for the prompt replies,

There's no critical reason not to unwind them. I was just curious as to whether oracle provided similar functionality.

The scenario is emulating some older sequential database.



I'm curious.
Does this syntax work for only numbers?
What about other datatypes such as strings, dates, etc?
Is this syntax in the ISO/ANSI SQL Standard?
Re: Row-wise comparison [message #341935 is a reply to message #341924] Wed, 20 August 2008 15:37 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Quote:
You could lpad each individual column with zeroes (in case of number-columns) up to a fixed length and then concatenate the two


I don't think I understand what you mean by this.
Re: Row-wise comparison [message #341936 is a reply to message #341929] Wed, 20 August 2008 15:40 Go to previous message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Quote:

Does this syntax work for only numbers?
What about other datatypes such as strings, dates, etc?


It works for any comparable data type.
Quote:

Is this syntax in the ISO/ANSI SQL Standard?



I'm not entirely sure, but the postgres documentation sure makes it sound like it is:

Quote:

Note: Prior to PostgreSQL 8.2, the <, <=, > and >= cases were not handled per SQL specification. A comparison like ROW(a,b) < ROW(c,d) was implemented as a < c AND b < d whereas the correct behavior is equivalent to a < c OR (a = c AND b < d).



... from the doc page I mentioned in my first post.
Previous Topic: problem with trigger
Next Topic: Write to file, send to email and pause...
Goto Forum:
  


Current Time: Wed Dec 07 16:39:03 CST 2016

Total time taken to generate the page: 0.09320 seconds