Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query Of Queries runtime error in ColdFusion

Posted on 2009-05-18
6
1,038 Views
Last Modified: 2013-12-20
I am getting the error in Cold Fusion 8
Query Of Queries runtime error.  
Unsupported Cast Excpetion: Casting to type NULL is unsupported.  
What i am doing is I have an include. If I dump include it is fine. bUT WHEN I DUMP THE QUERY OF Queries I am getting this error
<cfinclude template="QLoss_Answers.cfm">
      <cfdump var="#TempQuery#"> works fine
If I do this I am getting error
<cfquery name="TempQuery2" dbtype="query">
            Select * from TempQuery
      </cfquery>
<cfdump var="#TempQuery2#">
Please help

0
Comment
Question by:erikTsomik
  • 3
  • 2
6 Comments
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 24416388
Are you posting your real code and query?  If your tempQuery2 is just select * from tempQuery - why not just use tempQuery?  If your tempQuery2 is actually a bit more complex, could you post the entire query so we can help debug it?
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 24416418
OK.Here is my Include Qloss_answers.cfm
<cfset lSurveyID = "15,16,17,18">
<cfscript>
                  lPeople = createObject("component","CFC.getPeople").getAllSelectors(#url.GroupID#);
</cfscript>
<cfset TempQuery = QueryNew ("Q15,Comments15,Q16,Comments16,Q17,Comments17,Q18,Comments18,LastName,FirstName,JobCode,Description,LocationName,State,Employeeid")>
<cfquery name="GetRecords" datasource="cfelsunrise">
      Select  E.Employeeid,e.Last_name,E.First_name,eh.JobCode,j.Description,l.name,l.state
      from Employee e
      inner join EmployeeJobHistory eh on eh.EmployeeID = e.EmployeeID
      inner join JobAssignment ja on ja.Sequence = eh.Sequence
      inner join JobCode_list j on j.JobCode = eh.JobCode
      inner join Location l on l.LocationID = ja.LocationID
      where e.employeeid in (#lPeople#) and ja.EmplRcd = 0 and ja.EndDate is null
      order by e.Employeeid
</cfquery>


<cfloop query="GetRecords">
      <cfset newRow = QueryAddRow(TempQuery)>
      <cfset temp = QuerySetCell(TempQuery, "EmployeeID", Trim(GetRecords.EmployeeID))>
      <cfset temp = QuerySetCell(TempQuery, "LastName", Trim(GetRecords.Last_name))>
      <cfset temp = QuerySetCell(TempQuery, "FirstName", Trim(GetRecords.First_name))>
      <cfset temp = QuerySetCell(TempQuery, "JobCode", Trim(GetRecords.JobCode))>
      <cfset temp = QuerySetCell(TempQuery, "Description", Trim(GetRecords.Description))>
      <cfset temp = QuerySetCell(TempQuery, "State", Trim(GetRecords.State))>
      <cfset temp = QuerySetCell(TempQuery, "LocationName", Trim(GetRecords.name))>
      
      <cfloop list="#lSurveyID#" index="surveyID">
                        <cfquery name="qrySurveyReview" datasource="cfelsunrise">
                               Select * from surveys where id in (#surveyID#)
                        </cfquery>
                        <cfquery name="qryQuestionReview" datasource="cfelsunrise">
                         select * from questions where surveyidfk in (#surveyID#) order by sortOrder
                        </cfquery>
                        <cfquery name="qryCheck" datasource="cfelsunrise">
                              select * from survey_results where empID in (#GetRecords.EmployeeID#) and subjectID =0 and surveyIDfk in (#surveyID#) and completiondate is not null
                        </cfquery>
                              <cfif qryCheck.recordcount gt 0>
                                    <cfset lCheck = ValueList(qryCheck.id)>
                              <cfelse>
                                    <cfset lCheck = 0>
                              </cfif>
                                    <cfoutput query="qryQuestionReview" group="id">
                                                <cfif qryQuestionReview.recordcount gt 0>
                                                            <cfquery name="qryQuestionOptionsReview1" datasource="cfelsunrise">
                                                                  select * from  questionOptions  where questionID=#qryQuestionReview.id# order by sortOrder
                                                            </cfquery>
                                                            <cfset lReview1 = ValueList(qryQuestionOptionsReview1.id)>
                                                                  <cfloop list="#lReview1#" index="review">
                                                                        <cfquery name="qryAnswers" datasource="cfelsunrise">
                                                                              select a.answer,a.QuestionID  from  AnswerSurvey  a
                                                                              inner join SUrveyResultsDetail srdetail on srdetail.answer=a.id
                                                                              where QuestionID=#review# and srdetail.idfk in (#lCheck#)
                                                                        </cfquery>
                                                                        
                                                                              <cfif qryAnswers.recordcount gt 0>
                                                                                    <cfset temp = QuerySetCell(TempQuery, "Q#surveyID#",trim(qryAnswers.answer))>
                                                                              </cfif>
                                                                  </cfloop>
                                                </cfif>
                                                <cfif qryQuestionReview.isNarrative eq 1>
                                                      <cfquery name="narrative" datasource="cfelsunrise">
                                                            
                                                                  Select * from NarrativeAnswer n
                                                                  inner join Survey_results sr on sr.EmpID=n.Employeeid
                                                                  where sr.surveyidfk=#SurveyID#  and n.QuestionId= #qryQuestionReview.id#  
                                                                  and n.Employeeid in (#GetRecords.Employeeid#) and n.Employeeid !=999999999 and sr.completionDate is not null
<!---                                                                        Select * from NarrativeAnswer where surveyID=#SurveyID# and Employeeid=#GetRecords.Employeeid# and QuestionId= #qryQuestionReview.id#--->
                                                      </cfquery>                              
                                                      <cfif narrative.recordcount gt 0>
                                                            <cfset temp = QuerySetCell(TempQuery, "Comments#surveyID#",trim(narrative.NarrativeAnswer))>
                                                      
                                                      </cfif>
                                                </cfif>
                                    </cfoutput>
      </cfloop>      
</cfloop>

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24422287
Erik, it seem you've posted the wrong code or perhaps not all of the code.  There is no query of queries in the code you posted and no cfdump.   As usachris pointed out, if your query-of-query is just selected the query, there is no need for it to exist.

Also, when posting code, please use the [] attach code snippet box  so we can easily pull the code and scroll it with line numbers.

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 19

Author Comment

by:erikTsomik
ID: 24422381
the code that i posted is the include
THe calling page is this
<cfinclude template="QLoss_Answers.cfm">
<cfquery name="TempQuery" dbtype="query">
            Select * from TempQuery
      </cfquery>
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 24422490
You can delete this entirely...


<cfquery name="TempQuery" dbtype="query">
    Select * from TempQuery
</cfquery>


What was your intent?   It does nothing except waste processing time.

Just use the query variable  TempQuery  in any code below.

0
 
LVL 19

Author Comment

by:erikTsomik
ID: 24423255
thank you that works. However do you have an explanation why would I get this error. It used to work in CF7 and it does not in cf 8
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question