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:262389
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
