2 Replies Latest reply on Dec 11, 2012 3:25 AM by BKBK

    query

    kt03 Level 1

      I have the queries that get values from other two tables and then insert those values into the final tables.  My code below did not inserted the correct record.

      Thanks

       

      <!---list all  purNo, padNo where purNo = 3215--->
      <Cfquery name="getDetails" datasource="#dsn#">
      select purNo, padNo
          from tbl_details
          where purNo = 3215
      </Cfquery>


      <!---list all cusNO where purNo = 3215--->
      <Cfquery name="getCust" datasource="#dsn#">
      select cusNO
          from tbl_customer
          where purNo = 3215
      </Cfquery>

       

      From the form, I have one text box for user to enter new date then hit submit.  Once submit button is clicked, I would like to get the results from above two queries to insert into tblOrder.

       

      <cfset note ="new date changed from" />

       

      <cfloop query="getDetails">
      <cfloop query="getCust">

      <cfquery name="insert" datasource="#dsn#">

               insert into tblOrder (purNo, padNo, cusNO, note)

                  values

                  (

                <cfqueryparam value ="#getDetails.purNo#" />,

                 <cfqueryparam value ="#getDetails.padNo#" />,

                 <cfqueryparam value = "#getCust.cusNO#" />,

                 <cfqueryparam value="#note# #getDetails.old_ate# &'to' & #form.new_date#" />

                  )

                 

              </cfquery>

          </cfloop>
      </cfloop>

        • 1. Re: query
          Dan Bracuk Level 5

          Instead of nested loops, use this type of syntax.

           

          insert into tblOrder

          (field1, field2, etc)

          select somefield, someotherfield, etc
          from some other tables

          where some conditions are met

          • 2. Re: query
            BKBK Adobe Community Professional & MVP

            You could do a join, enabling you to use just one query. Something like

             

            <!--- space appended --->

            <cfset note ="new date changed from " />

             

            <Cfquery name="getCustDetails" datasource="#dsn#">

                select tbl_details.purNo, tbl_details.padNo, tbl_customer.cusNO

                from tbl_details, tbl_customer

                where tbl_details.purNo = tbl_customer.purNo

                and tbl_details.purNo = 3215

            </Cfquery>

             

            <cfloop query="getCustDetails">

             

            <!--- note: 'old_date' replaces 'old_ate' and " to " replaces "to" --->

            <cfset note = note & getCustDetails.old_date & " to " & form.new_date>

             

            <cfquery name="insert" datasource="#dsn#">

            insert into tblOrder (purNo, padNo, cusNO, note)

                values

                (

              <cfqueryparam value = "#getCustDetails.purno#" cfsqltype = "cf_sql_varchar" />,

               <cfqueryparam value = "#getCustDetails.padNo#" cfsqltype = "cf_sql_varchar" />,

               <cfqueryparam value = "#getCustDetails.cusNO#" cfsqltype = "cf_sql_varchar" />,

               <cfqueryparam value = "#note#" cfsqltype = "cf_sql_varchar" />

                )

            </cfquery>

             

            </cfloop>