Query of Query Data Casting Problem

[Guest]

Hey all.

I am running into an issue where when attempting to run a query of query I get a datatype conversion error. The field causing the problem is a zip code. Some records have a short zip code like 00000 and others have the full length zip code like 00000-0000. Of course Coldfusion being as smart as it is, if it sees a short version first assumes the column is an integer. Then if it hits a long version it explodes trying to convert them. I tried using casting doesn't it doesn't seem to help. This is my query.

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

Select Name AS CheckNum,

  Contact__r_name AS PayeeName,

  contact__r_MailingCity AS City,

  contact__r_MailingState AS State,

  contact__r_MailingStreet AS Street,

  CAST( contact__r_MailingPostalCode AS VARCHAR) AS ZIP,

  contact__r_MailingCountry AS Country,

  Payment_Amount__c AS PaymentValue,

  Session__r_Project_Number__c AS Memo,

  SESSION__R_ENDDATE AS CheckDate,

  contact__r_pid__c as PID,

  contact__r_Organization1__c AS Org

  From ChecksToMake.Results

</cfquery>

Any thoughts on what else I can do to prevent this error? Thanks!

Oh, btw the exact error in the last instance was

"The value 55311-3116 cannot be converted to a number."

It referenced the line that query starts on.

[Dan_Bracuk]

Did you try it without casting?  My experience is that ColdFusion preserves the datatypes from the original query.

[Guest]

Hi Ken,

Here is a solution but you'll end up adding another step to the process.

1).  Run your first query.

2).  Within CF, recreate the query using QueryNew, QueryAddRow, QuerySetCell.

3).  Return the output of that query.

Example:

      Step 1:

      <CFSET var data="">
  
      <CFQUERY NAME="data" DATASOURCE="#DSN#">
           SELECT DISTINCT #TP#_quadTable.Brand
        FROM #TP#_quadTable;
      </CFQUERY>

      Step 2.  
      <!--- Add Blank Row (For No Filter) & Sort for CFSelect --->
      <CFSET totalRows = (data.recordcount+1)>
      <CFSET getBrandsFilter = QueryNew("Brand", "VarChar")>
      <CFSET newRow = QueryAddRow(getBrandsFilter, totalRows)>
      <CFSET temp = QuerySetCell(getBrandsFilter, "Brand", "Brand Filter", 1)>
      <CFSET counter = 2>
      <CFLOOP QUERY="data">
         <CFSET temp = QuerySetCell(getBrandsFilter, "Brand", Brand, counter)>
         <CFSET counter = counter+1>
      </CFLOOP>

      (You could probably use a cfoutput here, I'm using cfreturn as it's withing a cffunction tag.)
      <CFRETURN getBrandsFilter>

*Ok, you're saying where is my solution:  Under Step 2, look at the second CFSET.  you see where you're calling out the "VarChar", that is what is telling CF how to output and should keep your errors from happening.  In that line, you'll end up calling your columns, then the type, so you might have

("City", "State", ZipCode", "VarChar", VarChar", VarChar")

Hope this helps.

<cfwild />

[Adam Cameron.]

Are you sure the error is coming from the QoQ, or is it from some code subsequently using the QoQ?

What's the exact error, including the context that it says it's occuring in, and the top of the stack trace?

--

Adam