Query Of Queries runtime error

[JayTee]

Query Of Queries runtime error.
Unsupported Cast Excpetion: Casting to type "NULL" is unsupported.

I am getting this error when I try to query a query for a search tool that I developed. Problem is, it doesn't happen everytime. And, after dumping the main query, before I query it (when the error happens), there is nothing visible in the query that indicates a NULL casting. We have even filtered out special characters, thinking that might be the problem.

I was wondering, what really causes this error, and is it something I am doing, or maybe a bug in CF7. I did not get this error until our company recently upgraded to CF7.

[paross1]

You didn't post any code, so it is harder to guess what is going on. Perhaps your main query is returning NULLs in a column that you are using in the WHERE clause in your Q-of-Q. You might try adding an IS NOT NULL statement in your Q-of-Q for any columns that may be returned as NULL from your database query. Or, return a valid value of the correct type in your datbase query if the column is NULL using IsNull(), etc.

Phil

[JayTee]

Thank for the Reply,

I didn't post any code because it is real general. But I will if it helps. We have tried the IS NOT NULL statement as well, even stripped the WHERE clause completly out,

<cftry>

<!---Get Category List-------------------------------------------------->
<cfset qryResultsAllCategoryList = Duplicate(stSearchStructure.results)>

<cfquery name="qryAllCategoryList"
dbtype="query">

SELECT Category
FROM qryResultsAllCategoryList
WHERE (groupLevel = <cfqueryparam cfsqltype="cf_sql_numeric" value="0"> or
groupLevel = <cfqueryparam cfsqltype="cf_sql_numeric" value="1">)
GROUP BY Category
</cfquery>

<cfset intCategoryListCount = qryAllCategoryList.recordcount>
<!---------------------------------------------------------------------->

<cfcatch type="any">

<cfset bDisplayResults = 0>

<!--- Display Error ---------------------------------------------------------------------->
<cfset stErrorArg = structNew()> <!--- Structure of aurguments to pass into function --->
<cfset stErrorArg.strMSG = "Cannot Search at this time. Please try again later.">
<cfset stErrorArg.strErrorDescMSG = "site_search.cfc; fnBuildResultsPage Criteria :
#stSearchStructure.Criteria#, User was NOT notified, silent error">
<cfset stErrorArg.bUseJS = 0>
<cfset stErrorArg.bUseHTML = 1>
<cfset stErrorArg.bSendEmailToProgrammer = 1>
<cfset stErrorArg.stDump = cfcatch>
<cfset stErrorArg.bUseErrorLog = 1>

<!--- Call function 'fnDynamicCFCCall' to call any specified component dynamically --->
<cfset application.generalFunctions.fnDynamicCFCCall
("CFComponets.error","fnDisplayError",stErrorArg>
<!----------------------------------------------------------------------------------------->

<!--- <cfdump var="#cfcatch#" label="cfcatch"><br><br> --->
<!---<cfdump var="#qryResultsAllCategoryList#" label="qryResultsAllCategoryList"><br>--->

</cfcatch>

</cftry>

[CF_Oracle]

Please put a master query for review.

[paross1]

This doesn't work?

SELECT Category
FROM qryResultsAllCategoryList
WHERE groupLevel IN (0, 1)
AND Category IS NOT NULL
GROUP BY Category

Phil

[JayTee]

I wish that would work, but still the same error.

[JayTee]

The master query is a custom query made from querys that actually touch the database. Its a search query, so I gather what I can from specific tables, and put them into my custom query with comman names, like title, description, ID, stuff like that. I try to keep the results (master query) as small as possible for performance.

Perhaps the custom query is the fault here?

[CF_Oracle]

Probably. Could you use just strait queries?

[JayTee]

The way things are designed, at the moment using straight queries would not work. I would have to re-implement the entire CFC, but if I can't get this problem solved, I may have to do it.

[JayTee · Accepted solution]

Just wanted to let everyone know the problem has been solved.

When I created the "Master Query", which is a Custom Query, I did not specify what type each field needed to be, so CF just assumes a type according to the value being placed into the first field. Let this be a lesson if you plan to merge Queries later, as I did.

CF assigned a type to a field, that had a number in it, as LONG. Well, later as I compiled queries, a particular row had a string value.
Everything was fine until I query the query, and try to select an integer value :

<cfquery name="qryAllCategoryList"
dbtype="query">
SELECT Category
FROM qryResultsAllCategoryList
WHERE (groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0"> or
groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="1">)
GROUP BY Category
</cfquery>

The query of queries failed at : groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0

So the fix is, I have to assign a type to every field in my Custom Query.
When I build the "Master Query" it should look like this :

<cfset qryResults = QueryNew("anchor,category,description,expire_date,isgroup,groupLevel,ID,last_updated,parentID,PID,queryString,results,title,URL","varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar")>

Now each field has its own type, in this case they are all VARCHAR.

Thanks for everyone's help! I hope I explained the answer good enough.

[Dan_Bracuk]

Cold Fusion converts null values to empty strings.

[Guest]

removed

[sheila2]

Thanks! You just solved my problem.