[rausten]

I am trying to build a kind of keyword search based on one form field. I am looking for a way to generate the sql query using the space as a delimiter. I am trying to use use CFLOOP to loop over the list and that almost works but it generates a line for entire index at once. In this case I get:

SELECT [id] ,[spannedtext] ,[PMID_DOI] ,title ,class FROM [dbo].[Knowtator] WHERE 1 = 1 
OR( [spannedtext] like AT2G37630% ) 
OR( [spannedtext] like ATSUC2% ) 

The Problem is in the logic of the query I need the first WHERE clause in this case to be an AND and any subsequent keywords to be OR.

Like so:

SELECT [id] ,[spannedtext] ,[PMID_DOI] ,title ,class FROM [dbo].[Knowtator] WHERE 1 = 1 
AND ( [spannedtext] like AT2G37630% ) 
OR( [spannedtext] like ATSUC2% ) 

My Code:

SELECT [id]
      ,[spannedtext]
	  ,[PMID_DOI]
	  ,title
	  ,class
  FROM [dbo].[Knowtator]
    WHERE 1 = 1
<cfloop list="AT2G37630 ATSUC2" delimiters=" " index="word" >
		OR( [spannedtext] like #word#% )	
</cfloop>

Any help on how to do this would be highly appreciated.

Ross

[Craig328]

Welcome to DIC rausten!

So, you're about 95% of the way there. You've already figured that you can insert CF code into the SQL statement in order to build dynamic SQL. You're just lacking the last part: changing the SQL operator from an AND to an OR after the first search.

Your code:

SELECT [id]
      ,[spannedtext]
	  ,[PMID_DOI]
	  ,title
	  ,class
  FROM [dbo].[Knowtator]
    WHERE 1 = 1
<cfloop list="AT2G37630 ATSUC2" delimiters=" " index="word" >
		OR( [spannedtext] like #word#% )	
</cfloop>

So, in your code you're on the money with the CFLOOP. All you need is some conditional code inside the loop to determine what iteration you're on within the loop. First iteration is an AND statement, everything is an OR, right?

Try this:

SELECT [id]
      ,[spannedtext]
	  ,[PMID_DOI]
	  ,title
	  ,class
  FROM [dbo].[Knowtator]
    WHERE 1 = 1
<cfset counter = 0>
<cfloop list="AT2G37630 ATSUC2" delimiters=" " index="word" >
     <cfset counter = counter + 1>
     <cfif counter EQ 1>
          AND( [spannedtext] like #word#% )
     <cfelse>
          OR( [spannedtext] like #word#% )
     </cfif>
</cfloop>

That's a very mechanical way of doing a conditional where statement per the loop iteration but this is easiest to see and understand if you're not accustomed. Try giving that a spin and see what you get.

Good luck!

[rausten]

Thanks that worked perfectly.

[Craig328]

N/P.

As an aside, you may want to look at the grouping that that code produces for your where statement.

That is, your where statement could end up looking like this:

WHERE 1 = 1 AND( [spannedtext] like #word#% ) OR( [spannedtext] like #word#% ) OR( [spannedtext] like #word#% ) OR( [spannedtext] like #word#% )

Make sure that's the where statement you want (an AND statement followed by an ungrouped OR series like that).