Using QOQ and LIKE Operator

[Michael42]

Hello,

I am trying to use a QOQ (Query of Queries) with the LIKE operataor. Here is my SQL:

<cfquery name="qry2" dbtype="query">SELECT * FROM qryInit WHERE location LIKE '%iam%' ORDER BY location</cfquery>

I am getting the following error:

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.

Null Pointers are another name for undefined values.
 
The error occurred in C:\CFusionMX\[URL unfurl="true"]wwwroot\dev\dbx\Application.cfm:[/URL] line 92

90 : 	
91 : 	<!--- <cfquery name="qry" dbtype="query">#preserveSingleQuotes(sSQL)#</cfquery> --->
92 : 	<cfquery name="qry" dbtype="query">SELECT * FROM qryInit WHERE location LIKE '%iam%' ORDER BY location</cfquery>
93 : 	
94 :

What can you recommend?

Thanks,

Michael42

 

[r937]

perhaps a little more info?

such as the contents of the qryInit query?

according to the cfdocs, LIKE is one of the supported features of q-of-q

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Michael42]

>> such as the contents of the qryInit query?

Yes, here is my complete function. It creates a query from a text file where each line in the text file is a record and each field is separated by the character "~". This is a working version as it does not have the WHERE clause.

<!--- Create Query From File (so SQL can be used on it (ORDER BY etc.) --->
<cffunction name="createQuery">
	<cfargument name="pTable">
	<cfargument name="pOrderBy">
	
	<!--- Init Local Vars --->
	<cfset row = "0">
	<cfset aFields = arrayNew(1)>
	<cfset iCounter = 1>

	<!--- Open Table and Assign Var --->
	<cfset sTable = openTable(pTable)>
	
	<!--- Get list of fields that this table consists of. --->
	<cfset lstTableFieldStructure = getProfileString(dbPath & "tables.ini", pTable & ".tbl", "fields")>
	
	<!--- Loop through each line\record in file\table and build a query. --->
	<cfset qryInit = QueryNew(lstTableFieldStructure)>
	<cfloop index="iRecord" list="#sTable#" delimiters="#dbREC#">
		<cfset iCounter = 1>
	
		<!--- Place list of field values into an array --->
		<cfset aFields = listToArray(iRecord, dbFLD)>
		
		<!--- Loop through each field in table structure and add value to query. --->
		<cfset x = queryAddRow(qryInit)>
		<cfloop index="iField" list="#lstTableFieldStructure#" delimiters=",">
			<cfset y = querySetCell( qryInit, iField, aFields[iCounter] )>
			<!--- Debug Line  
			<cfoutput>iCounter: #iCounter# | iField: #iField# | aFields[iCounter]:#aFields[iCounter]#</cfoutput><br>
			--->
			<cfset iCounter = iCounter + 1>
		</cfloop>
	</cfloop>
	
	<!--- Create Query of Query (QOQ) to be able to use ORDER BY --->
	<!--- Other SQL Operators (WHERE, LIKE etc.) TBD as CFMX bugs exists in QOQ --->
	<cfset sSQL = "SELECT " & lstTableFieldStructure & " FROM qryInit ORDER BY " & pOrderBy> 
	<cfquery name="qry" dbtype="query">#preserveSingleQuotes(sSQL)#</cfquery>

	<!--- Return query as variable --->
	<cfreturn qry>
</cffunction>

Thanks,

Michael42

 

[r937]

sorry, can't help you, i don't do functions, and i've never done a QueryNew()

however, somewhere in here is where your problem lies, at least, i would look here

your original error message was "undefined value" and since your q-of-q only mentioned the column name location i would investigate whether you're actually generating your query rows with a column of that name

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

yep, i did a "find" for "location" and didn't find it in your code. after you create your array you should use cfdump and see what's in it. You'll probably be supprised.

Beware of programmers who carry screwdrivers.

 

[Michael42]

Thanks guys - I really do appreciate your posts. I'll give the dump a go. See what REALLY goin on.

-Michael42