Can't find answer to Query Of Queries runtime error

[alecken]

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?

[LAW01]

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.

[alecken]

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.

[LAW01]

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.

[WarmFront]

HELP ! ! !  Going into testing soon. I need this to work to get correct report results ! ! ! !

My issue seems similar to the one under discussion and the reply from lawhite01 caught my eye. Can you roll my issue into this discussion?

This is a 2 parter. The second part is the QoQ part, but the 1st part has a line in the query that is similar to the QoQ one and it uses the same data. Part 1 also throws an error.

PART # 1.

I'm trying to use a query table created through QueryNew and then query it.

I need multiple columns in the query table I create:

<cfscript>
        tot_AllCurrentDraftListing = QueryNew("AnnounceNum, JP_PDLoc, JP_JS_Title, JP_JS, JP_KW_1, JP_JobTitle, JP_Open, JP_Close, JP_CloseType, JP_CloseName, JP_PosNeed, JP_DirectHire, JP_Desc, JP_Draft, JP_Archived, JP_State, JP_AreaName, JP_AreaID, JP_AreaAlias, JP_Fac_SU, JP_Fac_Facility, JP_FAC_ID, JP_Grade1, JP_sal_low1, JP_sal_high1, JP_Grade2, JP_sal_low2, JP_sal_high2, JP_Grade3, JP_sal_low3, JP_sal_high3, JP_Grade4, JP_sal_low4, JP_sal_high4, JP_Grade5, JP_sal_low5, JP_sal_high5, JP_Posted, JP_TypeHire, JP_HRemail");
    </cfscript>

......

Then I populate all the cells of the query table.

Then I set up to use the created query table.

I do this first:

    <cfquery name="qAltPostID" datasource="#at_datasource#">
         SELECT AltPoster, fk_Job_AnnounceNum
         from JOB_JPContacts
         Where AltJPContactType = 'AltPosterID'
         and AltPoster = '#session.IHSUID#'
         </cfquery>

Then, in my first query using the created query, I expect to need to choose from multiple values, so I'm using this line in the query (this is NOT a QoQ query):

            and AnnounceNum IN (<cfqueryparam cfsqltype="CF_SQL_varchar" value="#ValueList(qAltPostID.fk_Job_AnnounceNum)#">)

I've also tried:

               and AnnounceNum IN (#ValueList(qAltPostID.fk_Job_AnnounceNum)#)   

and:

               and JOB_AnnounceNum IN
            (
                SELECT fk_Job_AnnounceNum
                from JOB_JPContacts
                Where AltJPContactType = 'AltPosterID'
                and AltPoster = '#session.IHSUID#'
            )

ERROR is: one record should return. I get 0.

PART # 2: Here's the QoQ part.

I get the error:

Query Of Queries runtime error.
Comparison exception while executing IN.
Unsupported Type Comparison Exception: The IN operator does not support comparison between the following types:
Left hand side expression type = "LONG".
Right hand side expression type = "STRING".

A tutorial I found gave an example using only one column for this part of the fix:

     tot_AllCurrentDraftListing = QueryNew("AnnounceNum", "CF_SQL_VARCHAR")

How would I set up the query with the datatype when I'm using multiple columns:

<cfscript>
        tot_AllCurrentDraftListing = QueryNew("AnnounceNum, JP_PDLoc, JP_JS_Title, JP_JS, JP_KW_1, JP_JobTitle, JP_Open, JP_Close, JP_CloseType, JP_CloseName, JP_PosNeed, JP_DirectHire, JP_Desc, JP_Draft, JP_Archived, JP_State, JP_AreaName, JP_AreaID, JP_AreaAlias, JP_Fac_SU, JP_Fac_Facility, JP_FAC_ID, JP_Grade1, JP_sal_low1, JP_sal_high1, JP_Grade2, JP_sal_low2, JP_sal_high2, JP_Grade3, JP_sal_low3, JP_sal_high3, JP_Grade4, JP_sal_low4, JP_sal_high4, JP_Grade5, JP_sal_low5, JP_sal_high5, JP_Posted, JP_TypeHire, JP_HRemail");
    </cfscript>

I used this code after all the cells contained values and before running my QoQ query:

        <cfloop index="intID" from="1" to="#tot_AllCurrentDraftListing.recordcount#" step="1">
            <cfset tot_AllCurrentDraftListing["AnnounceNum"] [intID] = JavaCast("string", intID) />

        </cfloop>

          Is that correct?

Thanks.

Whoever can help me with this should be awarded extra points ! ! ! !

[Dan_Bracuk]

QueryNew() lets you specify datatypes.  Doing so could solve your datatyep issues.

For your part 1, I noticed you did not specify list="yes" in your cfqueryparam tag.  But since you tried other things and did not get the expected results, cfdump is your freind.   Concatonate something before and after your values.  Maybe you have leading or trailing space problems.

[Dan_Bracuk]

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>

[alecken]

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

[Dan_Bracuk]

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.

[BKBK · Accepted solution]

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

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

</cfquery>