Skip to main content
tclaremont
tclaremont
Inspiring
August 21, 2009
Question

Query of Queries Runtime Error

  • August 21, 2009
  • 2 replies
  • 3594 views

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?

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 22, 2009

    Perhaps DISTINCT(ICD9) instead?

    tclaremont
    tclaremontAuthor
    Inspiring
    August 24, 2009

    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?

    -
    -__cfSearching__-
    Inspiring
    August 21, 2009

    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
    tclaremontAuthor
    Inspiring
    August 21, 2009

    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
    tclaremontAuthor
    Inspiring
    August 21, 2009

    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.

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