Skip to main content
JayTee
Inspiring
Feb 27, 2008 2:46 PM UTC
Answered

Query Of Queries runtime error

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.
This topic is closed to new replies. Start a new post to keep the conversation going.
JayTeeCorrect answer
Inspiring
Mar 4, 2008 8:45 PM UTC
Probably. Could you use just strait queries?
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.
View reply

12 replies

Anonymous
Mar 1, 2008 3:08 AM UTC
removed
Translate
sheila2
Participating Frequently
Jul 8, 2020 9:03 AM UTC

Thanks! You just solved my problem.

Translate
Dan_Bracuk
Inspiring
Feb 27, 2008 4:43 PM UTC
Cold Fusion converts null values to empty strings.
Translate
CF_Oracle
Inspiring
Feb 27, 2008 4:00 PM UTC
Please put a master query for review.
Translate
paross1
Participating Frequently
Feb 27, 2008 4:06 PM UTC
This doesn't work?

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

Phil
Translate
JayTeeAuthor
Inspiring
Feb 27, 2008 4:38 PM UTC
I wish that would work, but still the same error.
Translate
paross1
Participating Frequently
Feb 27, 2008 3:00 PM UTC
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
Translate
JayTeeAuthor
Inspiring
Feb 27, 2008 3:47 PM UTC
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>
Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices