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

    query

    kt03 Community Member

      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 Community Member

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