Skip to main content
A
alecken
Known Participant
August 27, 2009
Answered

Can't find answer to Query Of Queries runtime error

  • August 27, 2009
  • 3 replies
  • 5127 views

Not only I browsed this forum but also googled this problem but unfortunnately I have no luck in finding the answer.

All I did was writing this simple query:

<cfquery name="test" dbtype="query">

select SSN,BirthDate from myquery where SSN <> '' OR BirthDate <> ''

</cfquery>

and I got this error:

Query Of Queries runtime error.

Comparison exception while executing <>.
Unsupported Type Comparison Exception: The <> operator does not support comparison between the following types:
Left hand side expression type = "DOUBLE".
Right hand side expression type = "STRING".

I tried the following and did not work either, still got the same error.

<cfquery name="test" dbtype="query">

select SSN,BirthDate from myquery

where SSN <> <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value=""/>

OR BirthDate <> <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value=""/></cfquery>

Has someone ever encountered the same problem and know how to solve it?

This topic has been closed for replies.
Correct answer BKBK

<cfquery name="test" dbtype="query">

select SSN,BirthDate from myquery where cast(SSN as varchar) <> '' OR cast(BirthDate as varchar) <> ''

</cfquery>

3 replies

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
August 28, 2009

<cfquery name="test" dbtype="query">

select SSN,BirthDate from myquery where cast(SSN as varchar) <> '' OR cast(BirthDate as varchar) <> ''

</cfquery>

D
Dan_Bracuk
Inspiring
August 27, 2009

Try this.  You'll get the same error, but hopefully you'll notice why.

<cfquery name="test" dbtype="query">

select SSN,BirthDate

from myquery

where 1000000 <> 'one million'

</cfquery>

A
aleckenAuthor
Known Participant
August 28, 2009

I tried it and the error is:

Query Of Queries runtime error.

Comparison exception while executing <>.
Unsupported Type Comparison Exception: The <> operator does not support comparison between the following types:
Left hand side expression type = "LONG".
Right hand side expression type = "STRING".

it looks like CF casts the SSN type to Long when running query to query and the problem is I can't change the datatype

Is there work around for this? I tried using convert to varchar and wasn't successful

D
Dan_Bracuk
Inspiring
August 28, 2009

To compare items of different datatypes, cold fusion has a cast function.  Usage is described in the cfml reference manual.

The is null vs <> '' scenario is one you'll have to play with.  In a database, if a numeric field is null, and you cast it to a string, the result will not be an empty string, it will be null.  I'm not sure how that works with cold fusion queries though.

L
LAW01
Participant
August 27, 2009

Try:

<cfquery name="test" dbtype="query">

select SSN,BirthDate from myquery where SSN is not null OR BirthDate <> ''

</cfquery>

It should fix the data type issue.

A
aleckenAuthor
Known Participant
August 28, 2009

You right, when I use IS NOT NULL to SSN I don't get the error but isn't it NOT NULL is not the same as <> '' where <> '' represent an empty string? or am I wrong?

The query that I'm querying against is built from a feed, it wasn't from a db.

L
LAW01
Participant
August 28, 2009

Fortunately or unfortunately I've found that Coldfusion will

automatically assign datatypes to columns in Query of Queries. I found

this out the hard way when CF was converting my string to a number.

The only way I've found to avoid this is to use QueryNew/QueryAddColumn,

etc. and manually set my own data types.

The simplest solution would most likely be to use "is not null", unless

you specifically need to preserve that data type.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices