Verity and hand-built queries

[Philip Arnold - ASP]

OK, here's the problem, I have a table of about 150 "events", each has it's
own table for it's content
To make them all globally searchable, I'm trying to build one huge Verity of
them (if I loop over the 150 verities, this takes around 45 seconds)

So, I start with a QueryNew() and loop over each of the tables and add them
to my query

Then I run a CFIndex on them;
<cfindex
        collection="Complete_Pages"
        action="Refresh"
        type="Custom"
        body="Title,Body"
        key="ID"
        title="Title"
        query="fullSQL"
        custom1="EventID"
        custom2="EventTitle">

The problem is that when I ask for a return from CFSearch it only returns
values from the very first table added to the query

If I search for "*" it should return everything, but only returns 9 rows
(the table from the first event in the list)

BTW, Just before I run the CFIndex I have outputted the query, and it looks
perfect - over 900 rows

Here's how I'm building the query;
<cfset fullSQL=QueryNew("ID,Title,Body,EventID,EventTitle")>
<cfloop query="getSiteList">
        <cfoutput>
        #EventID#<br>
        </cfoutput>
        <cfquery name="getPages" datasource="PagesDSN">
                select ID, Title, Body, '#EventID#' as EventID,
                        '#Replace(EventTitle,"'","''","all")#' as EventTitle
                from #EventID#_Pages
                where Title is not null
                order by Title desc
        </cfquery>
        <cfscript>
        for (i=1; i lte getPages.RecordCount; i=i+1)
        {       // add the rows
                RecCnt=QueryAddRow(fullSQL);
                QuerySetCell(fullSQL, "ID", getPages.ID[i], RecCnt);
                QuerySetCell(fullSQL, "Title", getPages.Title[i], RecCnt);
                QuerySetCell(fullSQL, "Body", getPages.Body[i], RecCnt);
                QuerySetCell(fullSQL, "EventID", EventID, RecCnt);
                QuerySetCell(fullSQL, "EventTitle", EventTitle, RecCnt);
        }
        </cfscript>
</cfloop>

This is bugging the hell out of me as I'm sure it should work, but it just
doesn't

Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133

"Websites for the real world"

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists