OraFAQ Forum: SQL & PL/SQL » The OR operator

Home » SQL & PL/SQL » SQL & PL/SQL » The OR operator

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The OR operator [message #304183]

Tue, 04 March 2008 08:08

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.

Report message to a moderator

Re: The OR operator [message #304186 is a reply to message #304183]

Tue, 04 March 2008 08:09

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I don't see any error and I don't see the code executed.

Report message to a moderator

Re: The OR operator [message #304187 is a reply to message #304183]

Tue, 04 March 2008 08:10

pablolee
Messages: 2882
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

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

Report message to a moderator

Re: The OR operator [message #304189 is a reply to message #304188]

Tue, 04 March 2008 08:15

joy_division
Messages: 4963
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

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

Report message to a moderator

Re: The OR operator [message #304192 is a reply to message #304191]

Tue, 04 March 2008 08:26

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

ok, another hint (really should have been obvious).

where a=1 or a=4;

Report message to a moderator

Re: The OR operator [message #304193 is a reply to message #304191]

Tue, 04 March 2008 08:27

Frank
Messages: 7901
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?

Report message to a moderator

Re: The OR operator [message #304199 is a reply to message #304192]

Tue, 04 March 2008 08:51

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

Report message to a moderator

Re: The OR operator [message #304208 is a reply to message #304199]

Tue, 04 March 2008 09:43

pablolee
Messages: 2882
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

joy_division
Messages: 4963
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

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: The OR operator [message #304318 is a reply to message #304295]

Tue, 04 March 2008 19:57

rleishman
Messages: 3728
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

Report message to a moderator

Re: The OR operator [message #304429 is a reply to message #304183]

Wed, 05 March 2008 03:11

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 10 00:02:17 CST 2025