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.
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.
WHERE ITEM = '#MID(QryGetInfo.custnoitem, 7,6)#'
in a single query would be
from table1 join table 2 on item = substr(custnoitem, 7, 6)
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.
Is there any reason your original query could not have had this?
where substr(custnoitem, 1, 5) = 'session.MemberID#'
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
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)?
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
ORDER BY custnoitem
<br /><br />
Standard Order <cfoutput>#session.MemberID#</cfoutput>
<br /><br />
<td>ITEM<br /> COMMITMENT</td>
<CFIF #left(QryGetInfo.custnoitem, 5)# eq #session.MemberID# AND #MID(QryGetInfo.custnoitem, 7,6)# LT 900000>
<cfquery name="GetSize" datasource="necsodbc">
SELECT item, size
WHERE left(item, 6) = '#MID(QryGetInfo.custnoitem, 7,6)#'
<cfif ASC(left(QryGetInfo.DESCRIP4,2)) NEQ 32>
<TD width="25"> </TD>