Query of Queries Error

Hi, I have a query of queries that has been working fine until today when I started getting the following error: Query Of Queries runtime error. Unsupported Numeric type conversion in Query of Queries. Thats the extent of the error message provided!  The columns seem to match up exactly on database type and length. Can anyone help provide some insight? Thanks, Sam

What version? Can you limit your select statement to one query that can cause the error? That will pinpoint the exact column. ======================================================================= Raymond Camden, ColdFusion Jedi Master for Hire Email    : jedimaster@macromedia.com Yahoo IM : morpheus "My ally is the Force, and a powerful ally it is." - Yoda ----- Excess quoted text cut - see Original Post for more -----

CF MX Enterprise. I have narrowed it down to one column and get the following message: All resulting columns of queries in a SELECT statement containing a UNION operator must have corresponding types. Columns with index number equal "2" have diffent types (INTEGER, VARCHAR). The column with an index of 2 in the second query is a column I have added, however, in this case it has no value.  In the first query the column is not added and its an integer column. Any suggestions? Thanks, Sam ----- Excess quoted text cut - see Original Post for more -----

It sounds like you are trying to link up a non integer and integer column from 2 queries. Can you make the added column have some default integer values? ======================================================================= Raymond Camden, ColdFusion Jedi Master for Hire Email    : jedimaster@macromedia.com Yahoo IM : morpheus "My ally is the Force, and a powerful ally it is." - Yoda ----- Excess quoted text cut - see Original Post for more -----

Thanks for you help Raymond.  I have it working by giving it a default value of -100.  Not ideal as really want it to be null.  As a suggestion it would be nice if the QueryAddColumn function took datatype as an argument. Cheers, Sam ----- Excess quoted text cut - see Original Post for more -----

I am getting the same Unsupported Numeric type error on a QofQ from a site I'm migrating from CF5 to CFMX. It worked fine in CF5. Mine sounds only slightly different in the fact that my QofQ is only used to order the query results. Notice I don't have all the "tie" fields in the QofQ, could that affect it? If anyone has any ideas or a better way to order the results, I'm all ears... [SNIP] <cfset newRow  = QueryAddRow(teamStandings, 1)>   <!--- set the cells in the query --->   <cfset temp = QuerySetCell(teamStandings, "site_id", "#site_id#", #loop_count#)>   <cfset temp = QuerySetCell(teamStandings, "name", "#organization_name#", #loop_count#)>   <cfset temp = QuerySetCell(teamStandings, "wins", "#wins#", #loop_count#)>   <cfset temp = QuerySetCell(teamStandings, "losses", "#losses#", #loop_count#)>   <cfset temp = QuerySetCell(teamStandings, "ties", "#ties#", #loop_count#)>   <cfset temp = QuerySetCell(teamStandings, "dist_wins", "#dist_wins#", #loop_count#)>   <cfset temp = QuerySetCell(teamStandings, "dist_losses", "#dist_losses#", #loop_count#)>   <cfset temp = QuerySetCell(teamStandings, "dist_ties", "#dist_ties#", #loop_count#)>   <cfset loop_count = loop_count + 1> </cfloop> <cfquery name="Standings" dbtype="query">   SELECT     site_id,     name,     wins,     losses,     dist_wins,     dist_losses   FROM teamStandings   ORDER BY dist_wins DESC </cfquery> Hi, I have a query of queries that has been working fine until today when I started getting the following error: Query Of Queries runtime error. Unsupported Numeric type conversion in Query of Queries. Thats the extent of the error message provided!  The columns seem to match up exactly on database type and length. Can anyone help provide some insight? Thanks, Sam

I've got a variation on this same error (Macromedia please acknowledge this is a bug in CFMX!) In my case I have an append query component that basically takes a query argument and appends the queries in an instance. The problem is that the query returned by the method may vary the data it returns. In this case at one point it returns most records with zero and 1 as data and so when the UNION tries to join them the error occurs. This is the error I get: Query Of Queries runtime error. All resulting columns of queries in a SELECT statement containing a UNION operator must have corresponding types. Columns with index number equal "23" have diffent types (VARCHAR, BIGINT). Jedi Master please tell this simple mortal: Why is CFMX 'assuming' data types in query of queries? (I've heard this complain many times, this makes code written in CF5 not compatible with CFMX) Why (if the sublime realm of Java requires that you use those ‘assumed’ data types) don’t you allow Query of queries to receive arguments that tell it what the data types are? Talk to me master… And lastly is there worldly solution to this problem? Trust me master, I’ve tried… but my faith fails me… if I change the data of those 2 insubordinate records to ‘Yes’ and ‘No’ my worldly code does work.

Can we see your code? ----- Excess quoted text cut - see Original Post for more -----

Here’s the function in a cfc that’s causing the error. It seems like the problem re-occurs whenever the runtime query being appended does not match the first one instantiated....:-( <cffunction name=”appendqry” returntype=”query” hint=”Appends a query in a named instance” output=”No”>        <cfargument name=”Query” type=”query” required=”Yes”> <cfargument name=”InstanceName” type=”string” default=”Query”> <cfif not isDefined(“this.#arguments.InstanceName#”)> <cfscript> “this.#arguments.InstanceName#” = arguments.query; </cfscript> <cfelse> <cfquery name=”this.#arguments.InstanceName#” dbtype=”query”> SELECT * FROM this.#arguments.InstanceName# UNION ALL SELECT * FROM arguments.Query </cfquery> </cfif> <cfreturn evaluate(“this.” & arguments.InstanceName) /> </cffunction>

Just to clarify...you are saying that a QoQ sometimes changes the datatype of a column? btw...you should be able to rewrite the cfreturn without the eval as: <cfreturn this[arguments.InstanceName] /> it's a bit cleaner imho. -- jon mailto:jonhall@ozline.net Tuesday, July 1, 2003, 4:52:23 PM, you wrote: JA> Here’s the function in a cfc that’s causing the error. JA> It seems like the problem re-occurs whenever the runtime query being appended does not match the first one instantiated....:-( JA> <cffunction name=”appendqry” JA> returntype=”query” JA> hint=”Appends a query in a named instance” output=”No”>                          JA> <cfargument name=”Query” type=”query” required=”Yes”> JA> <cfargument name=”InstanceName” type=”string” default=”Query”> JA> <cfif not isDefined(“this.#arguments.InstanceName#”)> JA> <cfscript> JA> “this.#arguments.InstanceName#” = arguments.query; JA> </cfscript> JA> <cfelse> JA> <cfquery name=”this.#arguments.InstanceName#” dbtype=”query”> JA> SELECT * FROM this.#arguments.InstanceName# JA> UNION ALL JA> SELECT * FROM arguments.Query JA> </cfquery> JA> </cfif> JA> <cfreturn evaluate(“this.” & arguments.InstanceName) /> JA> </cffunction> JA>

Thanks for the hint there. Yes the problem seems to be that CF creates the data types at run time and as far as I can tell based on the first row of the query. So depending on wich recordset is passed by the appendquery function, that particular column can change data types (in my case the data type is ntext in SQL and basically can accept any data type) but when CF sees mm/dd/yy it assumes it's a datetime data type, same thing with other data types as well. Just for the heck of it and based on a thread I read, I created this 'fix query' method that forces the first row to be string but it still will not work. So I am begining to think that saving the instance data on a Q of Q is not the best solution here but I really want to group the data so I welcome any suggestions....   <cffunction name="fixquery"              returntype="query"        hint="Ugly fix to CF Bug. Takes a query and returns a new one with a blank first row">     <cfargument name="Query" type="query" required="Yes">     <cfscript>       lstFields = arguments.query.Columnlist;       qTemp = QueryNew(lstFields);       QueryAddRow(qTemp,1);            </cfscript>     <cfloop index="ii" list="#lstFields#">       <cfscript>         QuerySetCell(qTemp,ii,'');       </cfscript>     </cfloop>                <cfloop query="arguments.Query">       <cfscript>         QueryAddRow(qTemp,1);       </cfscript>       <cfloop index="ii" list="#lstFields#">         <cfscript>           QuerySetCell(qTemp,ii,evaluate(ii));         </cfscript>       </cfloop>     </cfloop>     <cfreturn qTemp />   </cffunction>