0
Community Beginner
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/td-p/343852
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
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.
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.
TOPICS
Advanced techniques
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
1 Correct answer
Community Beginner
,
Mar 04, 2008
Mar 04, 2008
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 ...
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 ...
Mentor
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343853#M30951
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
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
Phil
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
JayTee
AUTHOR
Community Beginner
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343854#M30952
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
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>
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>
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Explorer
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343855#M30953
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
Please put a master query for review.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Mentor
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343856#M30954
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
This doesn't work?
SELECT Category
FROM qryResultsAllCategoryList
WHERE groupLevel IN (0, 1)
AND Category IS NOT NULL
GROUP BY Category
Phil
SELECT Category
FROM qryResultsAllCategoryList
WHERE groupLevel IN (0, 1)
AND Category IS NOT NULL
GROUP BY Category
Phil
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
JayTee
AUTHOR
Community Beginner
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343857#M30955
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
I wish that would work, but still the same error.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
JayTee
AUTHOR
Community Beginner
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343859#M30957
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
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?
Perhaps the custom query is the fault here?
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Explorer
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343860#M30958
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
Probably. Could you use just strait queries?
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
JayTee
AUTHOR
Community Beginner
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343861#M30959
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Beginner
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343863#M30961
Mar 04, 2008
Mar 04, 2008
Copy link to clipboard
Copied
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.
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
LEGEND
,
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343858#M30956
Feb 27, 2008
Feb 27, 2008
Copy link to clipboard
Copied
Cold Fusion converts null values to empty strings.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/343862#M30960
Feb 29, 2008
Feb 29, 2008
Copy link to clipboard
Copied
removed
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Beginner
,
LATEST
/t5/coldfusion-discussions/query-of-queries-runtime-error/m-p/11271942#M185873
Jul 08, 2020
Jul 08, 2020
Copy link to clipboard
Copied
Thanks! You just solved my problem.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

