Nested query doesn't return result

Report · Nov 20, 2009

Using CF 8.1. First field in table is "item" which is a compound filed containing a five digit customer number, a space, then a six digit item number. I need to produce a list of items for a specific customer based on their customer number, which is stored in a session variable. That part of my code all works. However, inside the output I need to retreive the item pack size from another table. This part of my code isn't working (QryAddInfo). Code pasted below. I'm wondering if it's because I'm trying to have a third query or that it is inside an output???

          <cfquery name="QryGetInfo" datasource="necsodbc">
          SELECT custnoitem, descrip, descrip4, lastdate
          FROM arcpric
          ORDER BY custnoitem
          </cfquery>

         <cfquery name="GetItems" dbtype="query">
          SELECT *
          FROM QryGetInfo
          WHERE #left(QryGetInfo.custnoitem, 5)# = #session.MemberID#
          ORDER BY custnoitem
          </cfquery>

<body>
<div align="center">
<br /><br />
Standard Order <cfoutput>#session.MemberID#</cfoutput>
<br /><br />
<table cellpadding="2">
<tr>
<td>ITEM</td>
<td>DESCRIPTION</td>
<td>PACK</td>
<td>ITEM<br /> COMMITMENT</td>
<td>QTY</td>
<td>LAST<BR />ORDERED</td>
</tr>
<cfoutput query="QryGetInfo">
<CFIF #left(QryGetInfo.custnoitem, 5)# eq #session.MemberID# AND #MID(QryGetInfo.custnoitem, 7,6)# LT 900000>
<TR height="22">
<TD>#MID(QryGetInfo.custnoitem, 7,6)#</TD>
<TD align="left">#QryGetInfo.DESCRIP#</TD>

<td>
<cfquery name="GetAddInfo" datasource="necsodbc">
SELECT SIZE
FROM ARINVT01
WHERE ITEM = '#MID(QryGetInfo.custnoitem, 7,6)#'
</cfquery>
#GetAddInfo.size#
</td>

<TD align="left">
<div align="center">
<cfif ASC(left(QryGetInfo.DESCRIP4,2)) NEQ 32>
YES
</cfif>
</div>
</TD>
<TD width="25"> </TD>
<TD>#QryGetInfo.LASTDATE#</TD>
</TR>
</CFIF>
</cfoutput>
</table>
</div>
</body>

Report · Nov 20, 2009

Your approach of running database queries inside a loop is very inefficient and shouldn't be necessary. A better approach would be to run a single query that gets the data from both tables. If you don't know how to do that, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

Report · Nov 20, 2009

The reason I didn't do it the "proper" way is because "size" can only be retreived once the item number is determined. I know what I have is inefficient, and that it is best to get all data with a single query, but without the item number being determined until after the second query it seemed this was not possible.

Report · Nov 20, 2009

this logic

WHERE ITEM = '#MID(QryGetInfo.custnoitem, 7,6)#'

in a single query would be

select fields

from table1 join table 2 on item = substr(custnoitem, 7, 6)

Report · Nov 20, 2009

Didn't work like that. The first thing is to get only the rows in the list attributed to a specific customer (session.MemberID). However to do this I had to use a function to get just the first 5 chars of the filed "custnoitem". Using the following WHERE clause in my inital query produced errors of custnoitem being undefined in the query.

WHERE #left(QryGetInfo.custnoitem, 5)# = #session.MemberID#

When I used it in a query of the initial query it produced what I needed, a list of all rows with items purchased by that customer. The first table "arcpric" has all the fields needed except "size" which is held in the inventory file "arinvt01". Only once you have determined the true item number "#MID(QryGetInfo.custnoitem, 7,6)#" can you look up the corresponding size in the second table.

Maybe I can't see the forest through the trees here and am just over thinking it.

Report · Nov 20, 2009

Is there any reason your original query could not have had this?

where substr(custnoitem, 1, 5) = 'session.MemberID#'

Report · Nov 20, 2009

That change gives the following error:

Macromedia][SequeLink JDBC Driver][ODBC Socket][Simba][SimbaEngine ODBC Driver]Invalid scalar function: substr.

The error occurred in C:\ColdFusion8\wwwroot\mccInternet\MembersOnly\StandardOrder.cfm: line 24

22 :           SELECT custnoitem, descrip, descrip4, lastdate
23 :           FROM arcpric
24 :           where substr(custnoitem, 1, 5) = #session.MemberID#
25 :           ORDER BY custnoitem
26 :           </cfquery>

SQLSTATE	42000
SQL	SELECT custnoitem, descrip, descrip4, lastdate FROM arcpric where substr(custnoitem, 1, 5) = 80200 ORDER BY custnoitem
DATASOURCE	necsodbc

Report · Nov 20, 2009

Progress.

I can see by your error message that you are using a simba database. I googled simba string functions and SE_FN_LEFT looks promising. What would happen if you replaced substr(custnoitem, 1, 5 ) with SE_FN_LEFT (custnoitem, 5)?

Report · Dec 01, 2009

Thanks for all your suggestions Dan. Though they weren't the answer, it made me question the validity of the ITEM field in the second table. Upon checking with the programmers of the application I found the field was padded with spaces. Which is why nothing matched. Your suggestion on substr was also close, but not quite. Here is what I came up with to produce the desired results.

          <cfquery name="QryGetInfo" datasource="necsodbc">
          SELECT custnoitem, descrip, descrip4, lastdate
          FROM arcpric
          ORDER BY custnoitem
          </cfquery>
<body>
<div align="center">
<br /><br />
Standard Order <cfoutput>#session.MemberID#</cfoutput>
<br /><br />
<table cellpadding="2">
<tr>
<td>ITEM</td>
<td>DESCRIPTION</td>
<td>PACK</td>
<td>ITEM<br /> COMMITMENT</td>
<td>QTY</td>
<td>LAST<BR />ORDERED</td>
</tr>

<cfoutput query="QryGetInfo">
<CFIF #left(QryGetInfo.custnoitem, 5)# eq #session.MemberID# AND #MID(QryGetInfo.custnoitem, 7,6)# LT 900000>
<TR height="22">
<TD>#MID(QryGetInfo.custnoitem, 7,6)#</TD>
<TD align="left">#QryGetInfo.DESCRIP#</TD>

<td>
      <cfquery name="GetSize" datasource="necsodbc">
          SELECT item, size
          FROM arinvt01
          WHERE left(item, 6) = '#MID(QryGetInfo.custnoitem, 7,6)#'
          </cfquery>

          #GetSize.size#
          </td>

<TD align="left">
<div align="center">
<cfif ASC(left(QryGetInfo.DESCRIP4,2)) NEQ 32>
YES
</cfif>
</div>
</TD>
<TD width="25"> </TD>
<TD>#QryGetInfo.LASTDATE#</TD>
</TR>
</CFIF>
</cfoutput>
</table>
</div>
</body>

Adobe Community

Nested query doesn't return result