I don't quite see the need for OR clauses. This all seems like AND
statements to me:
SELECT
meeting_name,
type,
note_date,
id,
n_r_id,
ROWNUM AS r,
COUNT (meeting_name) OVER() AS rowcount
FROM
notes_to_the_record
WHERE
ID = #session.user.id#
<cfif url.meeting_name neq "">
AND
UPPER(meeting_name) LIKE <cfqueryparam
value="%#UCase (url.meeting_name)#%" cfsqltype="cf_sql_varchar">
</cfif>
<cfif url.type neq "">
AND
#url.and_or_1# UPPER(type) LIKE <cfqueryparam
value="%#UCase (url.type)#%" cfsqltype="cf_sql_varchar">
</cfif>
<cfif url.notes neq "">
AND
#url.and_or_2# UPPER(notes) LIKE <cfqueryparam
value="%#UCase (url.notes)#%" cfsqltype="cf_sql_varchar">
</cfif>
ORDER BY
UPPER(meeting_name) ASC
..... However if you want to use OR statements for the CFIF parts, you
could do:
SELECT
meeting_name,
type,
note_date,
id,
n_r_id,
ROWNUM AS r,
COUNT (meeting_name) OVER() AS rowcount
FROM
notes_to_the_record
WHERE
ID = #session.user.id#
<cfif (
(url.meeting_name neq "") OR
(url.type neq "") OR
(url.notes neq "")
)>
AND
(
1 = 0
<cfif url.meeting_name neq "">
OR
UPPER(meeting_name) LIKE
<cfqueryparam value="%#UCase (url.meeting_name)#%"
cfsqltype="cf_sql_varchar">
</cfif>
<cfif url.type neq "">
OR
#url.and_or_1# UPPER(type) LIKE
<cfqueryparam value="%#UCase (url.type)#%" cfsqltype="cf_sql_varchar">
</cfif>
<cfif url.notes neq "">
OR
#url.and_or_2# UPPER(notes) LIKE
<cfqueryparam value="%#UCase (url.notes)#%" cfsqltype="cf_sql_varchar">
</cfif>
)
</cfif>
ORDER BY
UPPER(meeting_name) ASC
.... Lots of people have problems with the "1 = 0" type strategy... But
this works great and makes sense in my head.
......................
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, December 01, 2006 8:40 AM
To: CF-Talk
Subject: Query WHERE separation
I have a search that builds a query. It only returns records on the
users ID, so I want it to always require that ID be in the record. Then
I build a few AND or OR statements by which fields are selected. With
the AND, all is fine, but with the OR, it returns records of other IDs
cause it's a OR.
I figure the solution is to break the query into two parts, with another
select that only requires the ID wrapped around the built query for
fields. I have an idea how to do that but I am hoping that there is a
cleaner solution. Any thoughts?
Here's the query as is:
SELECT meeting_name,type,note_date,id,n_r_id, ROWNUM AS r, COUNT
(meeting_name) OVER() AS rowcount
FROM notes_to_the_record
WHERE 1=1 AND ID = #session.user.id#
<cfif url.meeting_name neq "">
AND UPPER(meeting_name) LIKE <cfqueryparam value="%#UCase
(url.meeting_name)#%" cfsqltype="cf_sql_varchar">
</cfif>
<cfif url.type neq "">
#url.and_or_1# UPPER(type) LIKE <cfqueryparam value="%#UCase
(url.type)#%" cfsqltype="cf_sql_varchar">
</cfif>
<cfif url.notes neq "">
#url.and_or_2# UPPER(notes) LIKE <cfqueryparam
value="%#UCase (url.notes)#%" cfsqltype="cf_sql_varchar">
</cfif>
ORDER BY UPPER(meeting_name) ASC
thanks.
daniel
- webguy -
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262394
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
