Home » SQL & PL/SQL » SQL & PL/SQL » invalid relational operator - Error
invalid relational operator - Error [message #284610] Fri, 30 November 2007 04:59 Go to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Frnds,

Can any one explain me the difference between the following SQLs ( Related to Performance ) ?

(1) select * from order
where
store in ('StoreA')

and (2) select * from order
where
('StoreA') in store.

Both gave me results successfully.

But when i included another value in query (2) as given below, it throws invalid relational operator Exception.

select * from order
where
('StoreA','StoreB') in store.

Can anyone please explain this behaviour ?

Thanks,
Bala.


Re: invalid relational operator - Error [message #284616 is a reply to message #284610] Fri, 30 November 2007 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some are valid syntax and other not.

SQL Reference
Chapter 7 Conditions
Section IN Condition

Regards
Michel
Re: invalid relational operator - Error [message #284618 is a reply to message #284616] Fri, 30 November 2007 05:31 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
thanks Michael,

Will be there be any performance difference between the 2 SQLs i have mentioned ?

(1) select * from order
where
store in ('StoreA')

and (2) select * from order
where
('StoreA') in store.
Re: invalid relational operator - Error [message #284630 is a reply to message #284618] Fri, 30 November 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Semantically none.
It is equivalent to "store = 'StoreA'" or "'StoreA' = store".

Regards
Michel
Re: invalid relational operator - Error [message #284646 is a reply to message #284630] Fri, 30 November 2007 07:07 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Michael,

I tried to understand the syntax error in the following SQL but could not make it.. is there anything wrong with the 'expression' in the left hand side of the comparison. could u plz xplain me ?

select * from order
where
('StoreA','StoreB') in store.
Re: invalid relational operator - Error [message #284648 is a reply to message #284610] Fri, 30 November 2007 07:16 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

@Balamurugan

Quote:

u plz xplain


??

Re: invalid relational operator - Error [message #284663 is a reply to message #284648] Fri, 30 November 2007 08:00 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Sorry, did get you ???
Re: invalid relational operator - Error [message #284665 is a reply to message #284663] Fri, 30 November 2007 08:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
from the forum guide

People don't feel encouraged when they have to decipher some gobbledygook.

Abbreviations like "U", "Ur", "U r", "Plz", "prb", "qry" especially tend to get on people's nerves.
IM speak
is not appreciated:
  • It is hard to read.
  • It is unprofessional.
  • It doesn't show much respect towards your fellow forum members.

If you don't take time to write in proper English, how can you expect that others would answer properly?
MHE
Re: invalid relational operator - Error [message #284667 is a reply to message #284610] Fri, 30 November 2007 08:17 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
yes i understand. This wont happen again.

Can Someone please clarify on this ?
Re: invalid relational operator - Error [message #284673 is a reply to message #284667] Fri, 30 November 2007 08:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
the manuals tell us

{ expr [ NOT ] IN ({ expression_list | subquery })
| ( expr 
    [, expr ]... 
    [ NOT ] IN ({ expression_list
                  [, expression_list ]...
                | subquery
                }
               )
  )
}
If you use the upper form of the in_condition condition (with a single expression to the left of the operator), then you must use the upper form of expression_list. If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of expression_list, and the expressions in each expression_list must match in number and datatype the expressions to the left of the operator.


In the left hand side you put either a single expression or a combination of expressions. This combination will be evaluated to a combination in the right hand side. Something like this:
SQL> select 'Yes'
  2  from   dual
  3  where  (1, 2) IN ( (2,2)
  4                   , (1,1)
  5                   , (3,2)
  6                   , (1,2)
  7                   )
  8  /

'YE
---
Yes
Unlike in some other expressions, with IN it does matter what you put on the right side and the left side. I hope this helps a little bit.

MHE

[Updated on: Fri, 30 November 2007 08:44]

Report message to a moderator

Re: invalid relational operator - Error [message #284675 is a reply to message #284610] Fri, 30 November 2007 08:52 Go to previous message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Yes..your explantion was very clear with a simple example .. Thank you Very Much.
Previous Topic: Problem with FULL OUTER JOIN
Next Topic: searching across two tables
Goto Forum:
  


Current Time: Thu Dec 08 18:47:51 CST 2016

Total time taken to generate the page: 0.10262 seconds