Query of Queries Runtime Error

[tclaremont]

I just moved an application from CF 7 to CF8. Just about everything is working dandy, except for one error so far that has me flummoxed.

Query of Queries runtime error

Unsupported SQL type java.sql.Types.UNKNOWN

My query is as follows:

<cfquery name="GetUnique" dbtype="query">
SELECT DISTINCT ICD9
FROM GetAll
</cfquery>

For clarification, I run a fairly complex query to retrieve a list of ICD9 codes for a given person in GetAll. Then I extract a DISTINCT list of those codes using the query above. And yes, there are reasons why I cant do this in a single query....

The above query works perfectly in CF7, and has been working for years. In my Google search, I have found a few instances where people are asking questions about the error, but I have yet to find a solution.

Any clues?

[-__cfSearching__-]

I do not have an answer.  But I assume you have already tried CAST'ing the values to a single data type?  Just to be thorough, you might also try a GROUP BY instead of DISTINCT.

[tclaremont]

Group By does not affect the outcome.

Regardless of what the solution turns out to be, why would something so inane break with the transition from CF7 to CF8?

Perhaps I am not up to date on patches and whatnot.

[tclaremont]

CASTing to the correct data type has no effct. Same error message. Note that CASTing to the WRONG data type results in an error message saying something about an invalid function.

[-__cfSearching__-]

It is hard to say without seeing the data and knowing the interpreted data types.  But QoQ have always been a bit quirky, so I am not totally surprised.

Out of curiousity, what are the values and underlying data types of the query values? You can check it using something like:

#queryName.icd9[rowNumber].getClass().name#

[BKBK]

Perhaps DISTINCT(ICD9) instead?

[tclaremont]

In the database (SQL Server 2005) the data type for ICD9 was NCHAR(10). I changed it to NVARCHAR and the query now works.

What is the Cliffs Notes difference between nchar and nvarchar that would allow this to work in CF7 but fail in CF8?