Home » SQL & PL/SQL » SQL & PL/SQL » The OR operator
The OR operator [message #304183] Tue, 04 March 2008 08:08 Go to next message
oraclestew
Messages: 13
Registered: December 2007
Location: Warrington, UK
Junior Member
I have to write a query with an 'and like' clause in it, but i now need to add the OR operator. However, when i do this my result set changes completely.
My current and statement is;
and c.promotioncode like 'C%'
but what i now need to do is as add
OR c.promotioncode like 'c%'.
If i try
and c.promotioncode like 'C%' or 'c%'
i get an error. Confused
How can i do this?
Any help gratefully received.
Re: The OR operator [message #304186 is a reply to message #304183] Tue, 04 March 2008 08:09 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I don't see any error and I don't see the code executed.
Re: The OR operator [message #304187 is a reply to message #304183] Tue, 04 March 2008 08:10 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please post exactly what you have tried. Remeber to format it and enclose the code in code tags (as demonstrated in the forum guide.)
Typing too slow

[Updated on: Tue, 04 March 2008 08:13]

Report message to a moderator

Re: The OR operator [message #304188 is a reply to message #304186] Tue, 04 March 2008 08:11 Go to previous messageGo to next message
oraclestew
Messages: 13
Registered: December 2007
Location: Warrington, UK
Junior Member
ok, code is:

select c.firstname, c.lastname, c.promotioncode,
a.idmmaccount, a.accountnumber, a.openingdate
from mm.mmaccount a, mm.mmcustomer c
where a.accountnumber like 'MU%'
and a.idmmcustomer=c.idmmcustomer
and c.promotioncode like 'C%' or 'c%';

error is:

and c.promotioncode like 'C%' or 'c%'
*
ERROR at line 6:
ORA-00920: invalid relational operator.

stew
Re: The OR operator [message #304189 is a reply to message #304188] Tue, 04 March 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Not formatted properly. Please read the forum guidelines.

This is hint on the proper way to use multiple operators in a WHERE clause:
where a=1 and a=4;

[Updated on: Tue, 04 March 2008 08:15]

Report message to a moderator

Re: The OR operator [message #304191 is a reply to message #304183] Tue, 04 March 2008 08:25 Go to previous messageGo to next message
oraclestew
Messages: 13
Registered: December 2007
Location: Warrington, UK
Junior Member
I have tried that, but if i do the following
  1  select c.firstname, c.lastname, c.promotioncode,
  2  a.idmmaccount, a.accountnumber, a.openingdate
  3  from mm.mmaccount a, mm.mmcustomer c
  4  where a.accountnumber like 'MU%'
  5  and a.idmmcustomer=c.idmmcustomer
  6* and c.promotioncode like 'C%'
SQL> /

FIRSTNAME  LASTNAME             PROMOTIONCODE                   
---------- -------------------- --------------------------------
mohammad   saud                 CHAMPIONS                       
testert    test                 CHAMPIONS                       
tester     test                 Champions                       


if i then add the extra and i get this;
SQL> ed
Wrote file afiedt.buf

  1  select c.firstname, c.lastname, c.promotioncode,
  2  a.idmmaccount, a.accountnumber, a.openingdate
  3  from mm.mmaccount a, mm.mmcustomer c
  4  where a.accountnumber like 'MU%'
  5  and a.idmmcustomer=c.idmmcustomer
  6  and c.promotioncode like 'C%'
  7* and c.promotioncode like 'c%'
SQL> /

no rows selected


and if i se the OR i get this error:
Wrote file afiedt.buf

  1  select c.firstname, c.lastname, c.promotioncode,
  2  a.idmmaccount, a.accountnumber, a.openingdate
  3  from mm.mmaccount a, mm.mmcustomer c
  4  where a.accountnumber like 'MU%'
  5  and a.idmmcustomer=c.idmmcustomer
  6* and c.promotioncode like 'C%' or 'c%'
SQL> /
and c.promotioncode like 'C%' or 'c%'
                                    *
ERROR at line 6:
ORA-00920: invalid relational operator


Thanks


Re: The OR operator [message #304192 is a reply to message #304191] Tue, 04 March 2008 08:26 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ok, another hint (really should have been obvious).
where a=1 or a=4;
Re: The OR operator [message #304193 is a reply to message #304191] Tue, 04 March 2008 08:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How can promotioncode start with a C _and_ with a c?
In other words: how can
and promotioncode like 'C%'
and promotioncode like 'c%'
ever return any rows?
Re: The OR operator [message #304199 is a reply to message #304192] Tue, 04 March 2008 08:51 Go to previous messageGo to next message
oraclestew
Messages: 13
Registered: December 2007
Location: Warrington, UK
Junior Member
i can't use the = sign, i must use the like clause, and that is the problem i'm having.
As you can see from my code i have to use the
and c.promotioncode like 'C%'


but if i add the
and c.promotioncode like 'C%' or 'c%'

i get an error.
SQL> select c.firstname, c.lastname, c.promotioncode, 
  2  a.idmmaccount, a.accountnumber, a.openingdate
  3  from mm.mmaccount a, mm.mmcustomer c
  4  where a.accountnumber like 'MU%'
  5  and a.idmmcustomer=c.idmmcustomer
  6  and c.promotioncode like 'C%' or 'c%';
and c.promotioncode like 'C%' or 'c%'
                                    *
ERROR at line 6:
ORA-00920: invalid relational operator



thanks
Re: The OR operator [message #304208 is a reply to message #304199] Tue, 04 March 2008 09:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
i can't use the = sign, i must use the like clause, and that is the problem i'm having.
Remember that LIKE is simply a relational operator just like =, >, < et al.
Stand back and take a deep breath. let's say that you wanted to restrict to see if c.promotioncodeA started with uppercase 'C' OR if c.promotioncodeB started with a lowercase 'c', how would you write that? (Just post the WHERE clause that you think would be appropriate)

[Updated on: Tue, 04 March 2008 09:43]

Report message to a moderator

Re: The OR operator [message #304267 is a reply to message #304199] Tue, 04 March 2008 12:46 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
oraclestew wrote on Tue, 04 March 2008 09:51
i can't use the = sign, i must use the like clause, and that is the problem i'm having.
As you can see from my code i have to use the
and c.promotioncode like 'C%'




Holy priceless collection of Etruscan snoods Batman, how much more obvious can I make it without writing the answer myself?
Last hint.
Have you ever heard of parenthesis for setting precedence in your WHERE clause and using valid syntax for a WHERE clause like I said?
And another hint as you just don't seem to get it:
Can you see the difference between
where a=1 or a=4;

and
where a=1 or 4;
???????

[Updated on: Tue, 04 March 2008 12:49]

Report message to a moderator

Re: The OR operator [message #304295 is a reply to message #304199] Tue, 04 March 2008 15:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Another obvious example:

SCOTT@orcl_11g> select *
2 from dual
3 where (dummy like 'X%' or dummy like 'x%')
4 /

D
-
X

SCOTT@orcl_11g>

You should be getting this by now.
Re: The OR operator [message #304318 is a reply to message #304295] Tue, 04 March 2008 19:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Looks as though you're just starting out learning SQL. You should take a look at the great manuals available at http://tahiti.oracle.com

There's even a SQL tutorial there.

Ross Leishman
Re: The OR operator [message #304429 is a reply to message #304183] Wed, 05 March 2008 03:11 Go to previous message
oraclestew
Messages: 13
Registered: December 2007
Location: Warrington, UK
Junior Member
Thanks everyone. I now have the answer.
and (c.promotioncode like 'C%' or c.promotioncode like 'c%')


I have just started learning SQL, which is why i logged it on the 'Newbies' section. I'll certainly have a look at the link you sent me.
Thnaks again.

Stew
Previous Topic: Select from table using an array
Next Topic: loading data into table from csv file with spaces between data
Goto Forum:
  


Current Time: Sun Dec 04 08:23:37 CST 2016

Total time taken to generate the page: 0.10837 seconds