Skip to main content
J
JayTee
Inspiring
February 27, 2008
Answered

Query Of Queries runtime error

  • February 27, 2008
  • 4 replies
  • 3276 views
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 has been closed for replies.
Correct answer JayTee
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.

4 replies

A
Anonymous
March 1, 2008
removed
S
sheila2
Participating Frequently
July 8, 2020

Thanks! You just solved my problem.

D
Dan_Bracuk
Inspiring
February 27, 2008
Cold Fusion converts null values to empty strings.
C
CF_Oracle
Inspiring
February 27, 2008
Please put a master query for review.
P
paross1
Participating Frequently
February 27, 2008
This doesn't work?

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

Phil
J
JayTeeAuthor
Inspiring
February 27, 2008
I wish that would work, but still the same error.
P
paross1
Participating Frequently
February 27, 2008
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
J
JayTeeAuthor
Inspiring
February 27, 2008
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>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices