8 Replies Latest reply on Dec 1, 2009 12:50 PM by ghouser

    Nested query doesn't return result

    ghouser Level 1

      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>

        • 1. Re: Nested query doesn't return result
          Dan Bracuk Level 5

          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.

          • 2. Re: Nested query doesn't return result
            ghouser Level 1

            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.

            • 3. Re: Nested query doesn't return result
              Dan Bracuk Level 5

              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)

              • 4. Re: Nested query doesn't return result
                ghouser Level 1

                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.

                • 5. Re: Nested query doesn't return result
                  Dan Bracuk Level 5

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

                   

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

                  • 6. Re: Nested query doesn't return result
                    ghouser Level 1

                    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

                    • 7. Re: Nested query doesn't return result
                      Dan Bracuk Level 5

                      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)?

                      • 8. Re: Nested query doesn't return result
                        ghouser Level 1

                        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>