Also,
From
what I have played with, you can not group by or order by TEXT columns, so
if your getting a whole lot of data from a table and one of your columns is a
TEXT columns, you will need to have the query convert it to VARCHAR before you
can do anything nifty with it.
Regards
Steve Onnis
Domain Concept Designs
+61 422
337 685
+61 3 9431 4249
| http://www.domainconceptdesigns.com [EMAIL PROTECTED] |
http://www.cfcentral.com.au [EMAIL PROTECTED] | |
|
|
("If you think it can't be done, you haven't asked me!") - Steve Onnis
--------Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of gary menzel
Sent: Thursday, March 13, 2003 10:02 AM
To: CFAussie Mailing List
Subject: [cfaussie] Re: SQL text vs varchar
Sure.....
I have a helpdesk application that I want to be able to do keyword searching on.
However, in the spirit of trying to make the database access "generic" (allowing lots of different views across the data in all sorts of grouped and ordered ways) I have created a SQL view to join a bunch of tables together (you know, things like Priority, Status, AssignedTo, AssignedBy, blah blah) and I access this view in my CFQUERY.....
Then I said to myself "This is a perfect time to get my hands dirty with full-text indexing" - generally I just use "like" across a couple of text fields and I have always been happy with the performance but always like to use the right tools for a job if I can.
So....
I set off and full-text index my main table in SQL*Server. This was a painless exercise - very easy to accomplish. I test this inside Enterprise Manager using the CONTAINS clause and I am very happy with the results.
Then I go back into my app and code into my generic routine the "CONTAINS" clause for my Title and Description fields.
So far so good.
Then I come to test it....... ERROR - can't do that because the "table" is not full-text indexed.
Then I am going "But I just set it all up and it worked fine".
Then I realise that I am not accessing the table directly - but through this almighty view.
So then..... I go back to Enterprise Manager and start looking at how I can full-text index my view......
YOU CANT.
So.... I either sacrifice my really kewl view that is performing very well and makes coding new filters and grouping and such VERY easy - or I go back to coding the joins up inside my CFQUERY, forgo the benefits of general indexing that a view provides, BUT get full-text search in return.
One solution to my problem would be this........
If a KEYWORD search (one requiring full-text indexing using the CONTAINS clause) is required then I do this search separately on the table itself and return a list of record id's that match, then use this against my view - limiting the records to those ones whose ID's are in the list previously retrieved (because there may be other conditions that are not KEYWORD based). This could be done as a sub-query in the WHERE clause.
Does this help ??
Gary Menzel
IT Operations Brisbane -+- ABN AMRO Morgans Limited
Level 29, 123 Eagle Street BRISBANE QLD 4000
PH: 07 333 44 828 FX: 07 3834 0828
[EMAIL PROTECTED] wrote on 03/13/2003 07:57:29 AM:
> "However, a word of warning, you cannot full text index SQL views in
> SQL*server - you have to do your search against the table columns
> directly."
>
> Can you explain the meaning of this, Gary ? I don't get it, and I might
> find it useful with a current project.
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to [EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
****************************************************************************
If this communication is not intended for you and you are not an authorised
recipient of this email you are prohibited by law from dealing with or
relying on the email or any file attachments. This prohibition includes
reading, printing, copying, re-transmitting, disseminating, storing or in
any other way dealing or acting in reliance on the information. If you
have received this email in error, we request you contact ABN AMRO Morgans
Limited immediately by returning the email to [EMAIL PROTECTED]
and destroy the original. We will refund any reasonable costs associated
with notifying ABN AMRO Morgans. This email is confidential and may contain
privileged client information. ABN AMRO Morgans has taken reasonable steps
to ensure the accuracy and integrity of all its communications, including
electronic communications, but accepts no liability for materials
transmitted. Materials may also be transmitted without the knowledge of ABN
AMRO Morgans. ABN AMRO Morgans Limited its directors and employees do not
accept liability for the results of any actions taken or not on the basis
of the information in this report. ABN AMRO Morgans Limited and its
associates hold or may hold securities in the companies/trusts mentioned
herein. Any recommendation is made on the basis of our research of the
investment and may not suit the specific requirements of clients.
Assessments of suitability to an individual's portfolio can only be made
after an examination of the particular client's investments, financial
circumstances and requirements.
****************************************************************************
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
