Home » SQL & PL/SQL » SQL & PL/SQL » Problem with LIKE
Problem with LIKE [message #278433] Sun, 04 November 2007 02:38 Go to next message
toenie007
Messages: 2
Registered: November 2007
Junior Member
Hello world,

I'm having this query problem with a sql statement. I try to get results based on user-input in a JSP page, which by the way I'm buidling in Jdeveloper 10.

Here's the somewhat simplified code:

SELECT 
    a1, b1, c1, d1
FROM 
    table a ,
    table b ,
    table c ,
    table d 
WHERE 
    UPPER(a1.a) LIKE  UPPER(:searchString)
   OR UPPER(b1.b) LIKE  UPPER(:searchString)
   OR UPPER(c1.c) LIKE  UPPER(:searchString)
   OR UPPER(d1.d) LIKE  UPPER(:searchString)   
AND id.a = id.d
AND id.b = id.d
AND id.c = id.d


Problem is that I get a carthesian product in my results.

Anayone has a clue?

Thx in advance.

[Updated on: Sun, 04 November 2007 02:54] by Moderator

Report message to a moderator

Re: Problem with LIKE [message #278434 is a reply to message #278433] Sun, 04 November 2007 02:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

id.a
Aren't you using Oracle?
table.column (or) column.table??

Anyway you need to put parenthesis around the OR conditions.

Thanks for putting code tags. But please follow 80 character limit per line.
 WHERE (UPPER(a.a1) LIKE UPPER(:searchString)
        OR UPPER(b.b1) LIKE UPPER(:searchString)
        OR UPPER(c.c1) LIKE UPPER(:searchString)
        OR UPPER(d.d1) LIKE UPPER(:searchString))
   AND id.a = id.d
   AND id.b = id.d
   AND id.c = id.d
By
Vamsi

[Updated on: Sun, 04 November 2007 02:57]

Report message to a moderator

Re: Problem with LIKE [message #278435 is a reply to message #278433] Sun, 04 November 2007 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Your query is syntaxically wrong
2/ If your query was equivalent to this one, you won't get a cartesian product unless Oracle estimates it is better to do it given the number of rows in each table (star transformation).

Regards
Michel
Re: Problem with LIKE [message #278438 is a reply to message #278433] Sun, 04 November 2007 03:40 Go to previous message
toenie007
Messages: 2
Registered: November 2007
Junior Member
Thx vamsi, sometimes life is so easy! Laughing
Previous Topic: solve the query
Next Topic: creating multiple LIKE
Goto Forum:
  


Current Time: Sat Dec 10 04:52:11 CST 2016

Total time taken to generate the page: 0.10496 seconds