search form not working when you choose "or"

[omerdurdu]

Can someone help me on about this. I have a search form with two operators "and" "or"
The query is working when I click "and" but not working when I click "or", it seems query not ending.
Here is the query:

SELECT Parkcode_XREF.Parkcode1, Parkcode_XREF.Proj_ID, Parkcode_XREF.Subject, ID.Dataset_ID, CI.Title, CI.Publication_Date, CO.Person_Name, ID.Abstract, Parkcode_XREF.Parkname1, Parkcode_XREF.Modifiedon, ID_Thesaurus_Keyword.Keyword_Name, MR.Standard_Name, ADDRESS.PARKCODE, ADDRESS.PARKNAME
FROM CO, CO_Address, CI, ID, Parkcode_XREF, MR, ID_Thesaurus, ID_Thesaurus_Keyword, Address
where CO.Contact_ID = CO_Address.Contact_ID AND CO.Contact_ID = CO_Address.Contact_ID and ID.Dataset_ID = Parkcode_XREF.Dataset_ID and ID.Dataset_ID = MR.Dataset_ID AND Parkcode_XREF.Dataset_ID = MR.Dataset_ID and CI.Citation_ID = ID.Citation_ID and CO.Contact_ID = ID.Contact_ID and ID_Thesaurus_Keyword.Thesaurus_ID=#q2.Thesaurus_ID# and #q3.Dataset_ID# = ID_Thesaurus.Dataset_ID and Parkcode_XREF.Parkcode1 = ADDRESS.PARKCODE and Parkcode='#park1.Parkcode#' and ID.Deleted like 0
<cfif search_for1 NEQ &quot;&quot;>
and (lower(Parkcode) like '%#lcase(search_for1)#%'
or lower(Abstract) like '%#lcase(search_for1)#%' or lower(Person_Name) like '%#lcase(search_for1)#%'
or lower(Title) like '%#lcase(search_for1)#%' or lower(Proj_ID) like '%#lcase(search_for1)#%' or lower(Subject) like '%#lcase(search_for1)#%')
</cfif>
<cfif search_for2 NEQ &quot;&quot;>
<cfif search_term2 EQ &quot;AND&quot;>
AND (lower(Parkcode) like '%#lcase(search_for2)#%'
or lower(Abstract) like '%#lcase(search_for2)#%' or lower(Person_Name) like '%#lcase(search_for2)#%'
or lower(Title) like '%#lcase(search_for2)#%' or lower(Proj_ID) like '%#lcase(search_for2)#%' or lower(Subject) like '%#lcase(search_for2)#%')
<cfelseif search_term2 EQ &quot;OR&quot;>
OR (lower(Abstract) like '%#lcase(search_for2)#%' or lower(Person_Name) like '%#lcase(search_for2)#%'
or lower(Title) like '%#lcase(search_for2)#%' or lower(Proj_ID) like '%#lcase(search_for2)#%' or lower(Subject) like '%#lcase(search_for2)#%')
</cfif>
</cfif>

 

[r937]

you need to parenthesize all your join conditions

when search_term2 is &quot;OR&quot; you effectively have

WHERE
whole bunch of ANDed join conditions
OR
( some search conditions )

since ANDs take precedence over ORs, the optimizer has to evaluate both sides of the OR equally

you are getting a cross-join in the OR side

no wonder the query never comes back

you would help matters by writing the query using JOIN syntax

put as many conditions into the ON clauses as you can


rudy

http://r937.com/

 

[omerdurdu]

I tried with putting parenthesies but it is not working

Can you tell me where am I gonna put the parenthesies in the code.
Thanks,
I am using oracle databases thatis why I use &quot;and&quot; not join
conditions

 

[r937]

i'm not going to rewrite your entire WHERE clause for you, but you should be able to figure it out from this:

WHERE ( join conditions )
AND ( ( some search conditions )
OR ( some more search conditions )
)

notice how the the entire search condition block is ANDed to the join condition block

as far as the JOIN syntax is concerned, oracle does support it, however i'm not sure how far back in earlier releases

if you're on oracle 9, you should try to rewrite your queries if you can

rudy