Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Query of Queries Error
Author: Jose Alfonso
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#126815
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>
Author: jon hall
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#126791
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>
Author: Jose Alfonso
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#126787
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>
Author: Matthew Walker
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#122340
Can we see your code?
----- Excess quoted text cut - see Original Post for more -----
Author: Jose Alfonso
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#122325
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.
Author: Ryan Kime
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#88035
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
Author: Sam Farmer
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#87870
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 -----
Author: Raymond Camden
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#87855
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 -----
Author: Sam Farmer
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#87851
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 -----
Author: Raymond Camden
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#87843
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 -----
Author: Sam Farmer
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:17099#87782
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
