Query Of Queries runtime error. Can't convert the string to java type DOUBLE

Not sure if anyone can help me on this unless they can see the dump of the data, but I will try.  I have a Custom Tag that retrieves data from a secured external DB.  It returns the requested data back in the form of a query.  I then do a Query of Query to display the info in the needed format.  This works 90% of the time but every now an then I get the QofQ runtime error when trying to query over the returned data.  When I CFDUMP the returned (qryShpDetail), it looks exactly like it should.  Can anytime spot a problem?

<!--- Query Of Queries runtime error. - Can't convert the string to java type DOUBLE  --->
<cfoutput>

<cfset Session.OH.ORDNO = 123456>
<cfset Session.bgHeader = 'green'>
<cfset Session.ShowShipments = 'Y'>

<!--- CF_SELECT is a custom tag to retrieve data from external db. It returns data in query form --->
<CF_SELECT SUCCESS="yesOrdDetail" ERRORS="noOrdDetail" QUERY="qryOrdDetail">
      SELECT       ORDNO, LINEN2, PRDNO, QTYORD, QTYSHP
      FROM       #tblOrdDetail#
      WHERE       ORDNO = #Session.OH.ORDNO#
</CF_SELECT>      
<cfif session.ShowShipments IS 'Y'>
      <CF_SELECT SUCCESS="yesShpDetail" ERRORS="noShpDetail" QUERY="qryShpDetail">
            SELECT       ORDNO, LINEN3, SSTAT, SEQNO, QTYSHP, SHPDT, ROLLNO
            FROM       #tblShpDetail#
            WHERE       (ORDNO = #Session.OH.ORDNO#) and (SSTAT = 'N' or SSTAT = 'S')
      </CF_SELECT>
</cfif>

<cfif yesOrdDetail>
<TABLE WIDTH="670" BGCOLOR="#Session.bgHeader#" BORDER="0" CELLPADDING="0" CELLSPACING="1">
<TR><TD>
<TABLE WIDTH="670" BGCOLOR="white" BORDER="0" CELLPADDING="0" CELLSPACING="0">                                    
<TR CLASS="userinfo">                  
      <TD ALIGN="left" WIDTH="25"><strong>Line</strong></TD>
      <TD ALIGN="left">&nbsp;</TD>                                    
      <TD ALIGN="left"><strong>Product</strong></TD>                                                      <TD ALIGN="right"><strong>Ordered</strong></TD>                                                      <TD ALIGN="right"><strong>Shipped</strong></TD>      
</TR>
<TR>
      <TD colspan="13" style="border-top: 2 solid green; font-size:2px">&nbsp;</TD>                                                                  
</TR>      
<cfloop query="qryOrdDetail">
<TR>
      <TD ALIGN="left">#qryOrdDetail.LINEN2#</TD>
      <TD ALIGN="left">&nbsp;</TD>                                                
      <TD ALIGN="left">#qryOrdDetail.PRDNO#</TD>                                                      <TD ALIGN="right">#NumberFormat(qryOrdDetail.QTYORD, ",___")#</TD>                                          <TD ALIGN="right">#NumberFormat(qryOrdDetail.QTYSHP, ",___")#</TD>                              
</TR>
<cfif session.ShowShipments IS 'Y'>
<cfif yesShpDetail>
<TR>                                          
<TD colspan="5">
<TABLE WIDTH=100% BORDER="0" CELLPADDING="0" CELLSPACING="1">
      <TR>
            <TD WIDTH="25">&nbsp;</TD>      
            <TD ALIGN="left">&nbsp;</TD>                                                                  <TD WIDTH="70" ALIGN="left"><strong>Roll No.</strong></TD>      
            <TD ALIGN="left">&nbsp;</TD>                                                                  <TD ALIGN="left"><strong>Ship Date</strong></TD>
            <TD ALIGN="right"><strong>Ship Qty</strong></TD>                              
            <TD ALIGN="right"></TD>                                                      
      </TR>

      <!--- <CFDUMP VAR=#qryOrdDetail#/><br> --->
      <!--- <CFDUMP VAR=#qryShpDetail#/><br> --->
      <!--- <cfabort> --->
      <!--- THIS IS THE QUERY THAT BOMBS --->
      <cfquery name="qryShipments" dbtype="query">
            Select            *
            From              qryShpDetail
            Where             LINEN3 = #qryOrdDetail.LINEN2#
      </cfquery>
                                                      
      <cfloop query="qryShipments">
      <TR>
            <TD WIDTH="25">&nbsp;</TD>      
            <TD ALIGN="left">&nbsp;</TD>                                                                  <TD WIDTH="70" ALIGN="left">
                                <cfif trim(qryShipments.ROLLNO) IS NOT "">#qryShipments.ROLLNO#
                                <cfelse>&nbsp;</cfif></TD>                                                                  <TD ALIGN="left">&nbsp;</TD>                                                                  <TD ALIGN="left">#DateFormat(qryShipments.SHPDT, "mm/dd/yy")#</TD>
            <TD ALIGN="right">#NumberFormat(qryShipments.QTYSHP, ",___")#</TD>
            <TD ALIGN="right"></TD>                                                            </TR>                                                
      </cfloop>
</TABLE>
</TD>
</TR>            
<cfelse>
<TR>                                          
      <TD colspan="5">
            <TABLE WIDTH=100% BORDER="0" CELLPADDING="0" CELLSPACING="1">
            <TR>
            <TD ALIGN="left">Shipment lines do not exist for this order.</TD>                                          <TD ALIGN="right"></TD>                                                                  </TR>      
            </TABLE>
      </TD>
</TR>      
</cfif>
</cfif>

</cfloop>
</TABLE>      
</TD></TR>                                          
</TABLE>
<cfelse>
No order lines for #Session.OH.ORDNO#<br>
</cfif>      
</cfoutput>
sulzenerAsked:
Who is Participating?
 
mrichmonConnect With a Mentor Commented:
Dain, interesting idea.  I think I tried that and it didn't work, but I can't be sure as it was awhile ago and I don't have access to that as it has since been re-written to a totally different method.

sulzener,  Here is some sample code:

<cfset qryShipments = QueryNew("col1,col2,col3,etc")>
<cfloop query="qryShpDetail">
    <cfif LINEN3 EQ qryOrdDetail.LINEN2>
          <cfset QueryAddRow(qryShipments)>
          <cfset QuerySetCell(qryShipments, "col1", qryShpDetail.col1)>
          <cfset QuerySetCell(qryShipments, "col2", qryShpDetail.col2)>
          <cfset QuerySetCell(qryShipments, "etc", qryShpDetail.etc)>
    </cfif>
</cfloop>

Then you can use qryShipments as a query in the rest of the code.

The main drawback is having to specify each column - you can't use * in this method
0
 
mrichmonConnect With a Mentor Commented:
I had that problem before - in one situation (almost identical to yours above) I could not get around it with Query of Queries.

Macromedia was no help either.

I had to have a loop and manually go through the query creating a new query by hand instead of using a query of queries....
0
 
sulzenerAuthor Commented:
Did you then query the newly created query to printout your data?  Also, I'd be interested in how you created the new query with a loop.  I am not sure how to do that.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Dain_AndersonCommented:
We had a similar issue as well, and the way we (think) it was solved was by surrounding the value in single quotes:

     <cfquery name="qryShipments" dbtype="query">
          Select          *
          From            qryShpDetail
          Where           LINEN3 = '#qryOrdDetail.LINEN2#'
     </cfquery>

It appears that QofQ doesn't really care if numeric values are in quotes. Anyway, it's worth a shot.

-Dain
0
 
rob_lorentzCommented:

its hard to tell, can you post the code for your <cf_select> custom tag?
0
 
sulzenerAuthor Commented:
I cant post the custum tag because it is copyrighted by an outside company, but I did build new queries from the returned queries just to see if I could bypass the error.  I still get the same error.  I played around and tried to load the new queries with non-stringed LINE NUMBERS (LINEN2, LINEN3).   This is so bizzare.  I have posted the modifed code below:

<cfoutput>
<cfset Session.OH.ORDNO = 123456>
<cfset Session.bgHeader = 'green'>
<cfset Session.ShowShipments = 'Y'>

<!--- CF_SELECT is a custom tag to retrieve data from external db --->
<CF_SELECT SUCCESS="yesOrdDetail" ERRORS="noOrdDetail" QUERY="qryOrdDetail">
SELECT       ORDNO, LINEN2, PRDNO, QTYORD, QTYSHP
FROM       #tblOrdDetail#
WHERE       ORDNO = #Session.OH.ORDNO#
</CF_SELECT>      
<cfif yesOrdDetail and ISQUERY(qryOrdDetail)>
<CFSET LoopCount=1>
<cfset session.OD = QueryNew("LINEN2, PRDNO, QTYORD, QTYSHP")>
<CFLOOP QUERY="qryOrdDetail">
<cfset temp = QueryAddRow(session.OD)>
<cfset temp = QuerySetCell(session.OD,"LINEN2",#Replace(NumberFormat(qryOrdDetail.LINEN2), ",","","ALL")#,"#LoopCount#")>
<cfset temp = QuerySetCell(session.OD,"PRDNO","#qryOrdDetail.PRDNO#","#LoopCount#")>      
<cfset temp = QuerySetCell(session.OD,"QTYORD","#qryOrdDetail.QTYORD#","#LoopCount#")>            
<cfset temp = QuerySetCell(session.OD,"QTYSHP","#qryOrdDetail.QTYSHP#","#LoopCount#")>                                                                        
<CFSET #LoopCount#=#LoopCount#+1>
</CFLOOP>
</cfif>      
<cfif session.ShowShipments IS 'Y'>
<CF_SELECT SUCCESS="yesShpDetail" ERRORS="noShpDetail" QUERY="qryShpDetail">
SELECT       ORDNO, LINEN3, SSTAT, SEQNO, QTYSHP, SHPDT, LOT4D AS ROLLNO
FROM       #tblShpDetail#
WHERE       (ORDNO = #Session.OH.ORDNO#) and (SSTAT = 'N' or SSTAT = 'S')
</CF_SELECT>

<cfif yesShpDetail and ISQUERY(qryShpDetail)>
<CFSET LoopCount=1>
<cfset session.SD = QueryNew("ORDNO,LINEN3,SSTAT,SEQNO,QTYSHP,SHPDT,ROLLNO")>
<CFLOOP QUERY="qryShpDetail">
<cfset temp = QueryAddRow(session.SD)>
<cfset temp = QuerySetCell(session.SD,"ORDNO","#qryShpDetail.ORDNO#","#LoopCount#")>
<cfset temp = QuerySetCell(session.SD,"LINEN3",#Replace(NumberFormat(qryShpDetail.LINEN3), ",","","ALL")#,"#LoopCount#")>
<cfset temp = QuerySetCell(session.SD,"SSTAT","#qryShpDetail.SSTAT#","#LoopCount#")>
<cfset temp = QuerySetCell(session.SD,"SEQNO","#qryShpDetail.SEQNO#","#LoopCount#")>
<cfset temp = QuerySetCell(session.SD,"QTYSHP","#qryShpDetail.QTYSHP#","#LoopCount#")>
<cfset temp = QuerySetCell(session.SD,"SHPDT","#qryShpDetail.SHPDT#","#LoopCount#")>
<cfset temp = QuerySetCell(session.SD,"ROLLNO","#qryShpDetail.ROLLNO#","#LoopCount#")>
<CFSET #LoopCount#=#LoopCount#+1>
</CFLOOP>
</cfif>      

</cfif>

<cfif ISQUERY(session.OD)>
<TABLE WIDTH="670" BGCOLOR="#Session.bgHeader#" BORDER="0" CELLPADDING="0" CELLSPACING="1">
<TR><TD>
<TABLE WIDTH="670" BGCOLOR="white" BORDER="0" CELLPADDING="0" CELLSPACING="0">                                    
<TR CLASS="userinfo">                  
<TD ALIGN="left" WIDTH="25"><strong>Line</strong></TD>
<TD ALIGN="left">&nbsp;</TD>                                    
<TD ALIGN="left"><strong>Product</strong></TD>                                                                                                      
<TD ALIGN="right"><strong>Ordered</strong></TD>                                                                                                      
<TD ALIGN="right"><strong>Shipped</strong></TD>      
</TR>
<TR>
<TD colspan="13" style="border-top: 2 solid green; font-size:2px">&nbsp;</TD>                                                                  
</TR>      
<cfloop query="qryOrdDetail">
<TR>
<TD ALIGN="left">#session.OD.LINEN2#</TD>
<TD ALIGN="left">&nbsp;</TD>                                                
<TD ALIGN="left">#session.OD.PRDNO#</TD>                                                                              
<TD ALIGN="right">#NumberFormat(session.OD.QTYORD, ",___")#</TD>                                                                        
<TD ALIGN="right">#NumberFormat(session.OD.QTYSHP, ",___")#</TD>                                                                                    
</TR>
<cfif session.ShowShipments IS 'Y'>
<cfif ISQUERY(session.SD)>
<TR>                                          
      <TD colspan="20">
            <TABLE WIDTH=100% BORDER="0" CELLPADDING="0" CELLSPACING="1">
                  <TR>
                        <TD WIDTH="25">&nbsp;</TD>      
                        <TD ALIGN="left">&nbsp;</TD>                                                            
                        <TD WIDTH="70" ALIGN="left"><strong>Roll No.</strong></TD>      
                        <TD ALIGN="left">&nbsp;</TD>                                                                                                                        
                        <TD ALIGN="left"><strong>Ship Date</strong></TD>
                        <TD ALIGN="right"><strong>Ship Qty</strong></TD>                              
                        <TD ALIGN="right"></TD>                                                                                                                                                                  
                  </TR>

                  <cfquery name="qryShipments" dbtype="query">
                        Select            *
                        From              session.SD
                        Where             LINEN3 = #session.OD.LINEN2#
                  </cfquery>
                                                                  
                  <cfloop query="qryShipments">
                  <TR>
                        <TD WIDTH="25">&nbsp;</TD>      
                        <TD ALIGN="left">&nbsp;</TD>                                                            
                        <TD WIDTH="70" ALIGN="left"><cfif trim(qryShipments.ROLLNO) IS NOT "">#qryShipments.ROLLNO#<cfelse>&nbsp;</cfif></TD>                                                                  
                        <TD ALIGN="left">&nbsp;</TD>                                                                  
                        <TD ALIGN="left">#DateFormat(qryShipments.SHPDT, "mm/dd/yy")#</TD>
                        <TD ALIGN="right">#NumberFormat(qryShipments.QTYSHP, ",___")#</TD>
                        <TD ALIGN="right"></TD>                                                                                                                                                                  
                  </TR>                                                
                  </cfloop>
            </TABLE>
      </TD>
</TR>            
<cfelse>
<TR>                                          
      <TD colspan="20">
            <TABLE WIDTH=100% BORDER="0" CELLPADDING="0" CELLSPACING="1">
                  <TR>
                        <TD ALIGN="left">Shipment lines do not exist for this order.</TD>                                                                                          
                        <TD ALIGN="right"></TD>                                                                                                                                                                  
                  </TR>      
            </TABLE>
      </TD>
</TR>      
</cfif>                                                                                                                                                                              
</cfif>

</cfloop>
</TABLE>      
</TD></TR>                                          
</TABLE>
<cfelse>
No order lines for #Session.OH.ORDNO#<br>
</cfif>      
</cfoutput>
0
 
sulzenerAuthor Commented:
I am wondering is someone could comment on my last post?  After puttingthe data into totally different queries, the error still occurs. I am wondering if someone could even help me figure out how to determine ifa specific field might be causing the error?  Is it the WHERE clause?  At this point I'm stumped.
0
 
sulzenerAuthor Commented:
Problem was resolved by checking each fields before loading into new queries.  Narrowed it down to the ROLLNO field in Session.SD.  It contained numbers until the last field.  Problem was ultimately resolved by using mrichmon's suggestion.  Thanks to all.
0
All Courses

From novice to tech pro — start learning today.