• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

query

Explorer ,
Dec 03, 2012 Dec 03, 2012

Copy link to clipboard

Copied

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>

TOPICS
Getting started

Views

716

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 03, 2012 Dec 03, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 11, 2012 Dec 11, 2012

Copy link to clipboard

Copied

LATEST

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation