asked on

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?


<cfoutput>

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


<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"> </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"> </TD>
</TR>
<cfloop query="qryOrdDetail">
<TR>
      <TD ALIGN="left">#qryOrdDetail.LINEN2#</TD>
      <TD ALIGN="left"> </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"> </TD>
            <TD ALIGN="left"> </TD>                                                                  <TD WIDTH="70" ALIGN="left"><strong>Roll No.</strong></TD>
            <TD ALIGN="left"> </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            qryShpDetail
            Where             LINEN3 = #qryOrdDetail.LINEN2#
      </cfquery>

      <cfloop query="qryShipments">
      <TR>
            <TD WIDTH="25"> </TD>
            <TD ALIGN="left"> </TD>                                                                  <TD WIDTH="70" ALIGN="left">
<cfif trim(qryShipments.ROLLNO) IS NOT "">#qryShipments.ROLLNO#
<cfelse> </cfif></TD>                                                                  <TD ALIGN="left"> </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>

SOLUTION

mrichmon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sulzener

ASKER

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.

Dain_Anderson

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

rob_lorentz

its hard to tell, can you post the code for your <cf_select> custom tag?

ASKER CERTIFIED SOLUTION

mrichmon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sulzener

ASKER

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 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"> </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"> </TD>
</TR>
<cfloop query="qryOrdDetail">
<TR>
<TD ALIGN="left">#session.OD.LINEN2#</TD>
<TD ALIGN="left"> </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"> </TD>
                        <TD ALIGN="left"> </TD>
                        <TD WIDTH="70" ALIGN="left"><strong>Roll No.</strong></TD>
                        <TD ALIGN="left"> </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"> </TD>
                        <TD ALIGN="left"> </TD>
                        <TD WIDTH="70" ALIGN="left"><cfif trim(qryShipments.ROLLNO) IS NOT "">#qryShipments.ROLLNO#<cfelse> </cfif></TD>
                        <TD ALIGN="left"> </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>

sulzener

ASKER

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.

sulzener

ASKER

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.