Using CFLOOP to insert multiple records

[szeiss]

I'm using MX7 with Oracle 9i. I don't use loops very often and am
having trouble with this one. The problem is I can't seem to get
all the contracts inserted. I either get only the first contract
inserted or I get repeating contracts. Total records in getPROJECTS
is 617. Records in getCONTRACTS will vary per getPROJECTS record,
some getPROJECTS will have 2 contracts, some 5 and so on.


The inserted records should look something like this:

getPROJECTS.project1 getCONTRACTS.contract1
getPROJECTS.project1 getCONTRACTS.contract2
getPROJECTS.project1 getCONTRACTS.contract3
getPROJECTS.project2 getCONTRACTS.contract1
getPROJECTS.project3 getCONTRACTS.contract1
getPROJECTS.project3 getCONTRACTS.contract2
getPROJECTS.project4 getCONTRACTS.contract1
getPROJECTS.project4 getCONTRACTS.contract2
getPROJECTS.project4 getCONTRACTS.contract3
getPROJECTS.project4 getCONTRACTS.contract4

Here are my partial queries:

<cfquery datasource="#Application.rdsn#" name="getPROJECTS">
SELECT
a.subproject_name,
a.subproject_id,
a.current_phase,
b.description,
c.fullname,
a.subproject_design_consultant,
a.subproject_contractor
FROM
cip.subprojects a,
cip.list_project_types b,
cip.list_employees c
WHERE
a.PROJECT_TYPE(+) =
b.PROJECT_TYPE
AND a.EMPLOYEE_ID = c.EMPLOYEE_ID
AND a.SUBPROJECT_CLOSED = 'N'
AND a.DEPT_CD = '#variables.DEPTCD#'
</cfquery>


<cfloop query="getPROJECTS">
<cfquery datasource="#Application.rdsn#" name="getCONTRACTS">
SELECT DISTINCT
qTotal.AFS3_contract_type,
qTotal.AFS3_contract_no,
qTotal.AFS2_contract_type,
qTotal.AFS2_contract_no,
qTotal.solicitation_no,
qTotal.extended_cost,
qTotal.award_date,
qTotal.Closed_Date,
qTotal.vend10,
qTotal.vendor_name1,
qFAOs.Subproject_id,
qSOL.EST_CONTRACT_DUR,
qSOL.SOLICITATION_ID,
qSOL.RCA_AMOUNT
FROM
(
SELECT DISTINCT
cip.contract_headers_afs3.AFS3_Contract_Type,
cip.contract_headers_afs3.AFS3_Contract_No,
cip.CONTRACT_HEADERS_AFS2.CONTRACT_TYPE AS AFS2_Contract_Type,
cip.CONTRACT_HEADERS_AFS2.CONTRACT_NO AS AFS2_Contract_No......
</CFQUERY>

</cfloop>

<cfset v_subproject_name = getPROJECTS.subproject_name>
<cfset v_subproject_id = getPROJECTS.subproject_id>
<cfset v_current_phase = getPROJECTS.current_phase>
<cfset v_projtype = getPROJECTS.description>
<cfset v_fullname = getPROJECTS.fullname>
<cfset v_consultant = getPROJECTS.subproject_design_consultant>

<cfset v_afs3contract_type = getCONTRACTS.AFS3_contract_type>
<cfset v_afs3contract_no = getCONTRACTS.AFS3_contract_no

<cfset v_afs2contract_type = getCONTRACTS.AFS2_contract_type>
<cfset v_afs2contract_no = getCONTRACTS.AFS2_contract_no>


<cfquery datasource="#Application.rdsn#" name="InsertData">

INSERT INTO newtable
(
subproject_name,
subproject_id,
current_phase,
project_type
)
VALUES
(
'#v_subproject_name#',
#v_subproject_id#,
'#v_current_phase#',
'#v_projtype#'
)
</cfquery>
</cfloop>

Thanks for the help.

 

[cfStarlight]

You would need to use 2 loops. The 1st to loop through the getProjects query results and the 2nd to loop through the getCONTRACTS query results. The insert would need to be nested inside the 2nd loop.

loop through getProjects query
-- run getContractsQuery
---- loop through getContractsQuery
------ run INSERT statements

But is there a reason you cannot combine the queries to avoid the nested looping?

 

[szeiss]

If I following you correctly, this still doesn't work.


<cfquery datasource="#Application.rdsn#" name="getPROJECTS">
SELECT
a.subproject_name,
a.subproject_id,
a.current_phase,
b.description,
c.fullname,
a.subproject_design_consultant,
a.subproject_contractor
FROM
cip.subprojects a,
cip.list_project_types b,
cip.list_employees c
WHERE
a.PROJECT_TYPE(+) =
b.PROJECT_TYPE
AND a.EMPLOYEE_ID = c.EMPLOYEE_ID
AND a.SUBPROJECT_CLOSED = 'N'
AND a.DEPT_CD = '#variables.DEPTCD#'
</cfquery>


<cfloop query="getPROJECTS">
<cfquery datasource="#Application.rdsn#" name="getCONTRACTS">
SELECT DISTINCT
qTotal.AFS3_contract_type,
qTotal.AFS3_contract_no,
qTotal.AFS2_contract_type,
qTotal.AFS2_contract_no,
qTotal.solicitation_no,
qTotal.extended_cost,
qTotal.award_date,
qTotal.Closed_Date,
qTotal.vend10,
qTotal.vendor_name1,
qFAOs.Subproject_id,
qSOL.EST_CONTRACT_DUR,
qSOL.SOLICITATION_ID,
qSOL.RCA_AMOUNT
FROM
(
SELECT DISTINCT
cip.contract_headers_afs3.AFS3_Contract_Type,
cip.contract_headers_afs3.AFS3_Contract_No,
cip.CONTRACT_HEADERS_AFS2.CONTRACT_TYPE AS AFS2_Contract_Type,
cip.CONTRACT_HEADERS_AFS2.CONTRACT_NO AS AFS2_Contract_No......
</CFQUERY>

</cfloop>

<cfloop query="getCONTRACTS">

<cfset v_subproject_name = getPROJECTS.subproject_name>
<cfset v_subproject_id = getPROJECTS.subproject_id>
<cfset v_current_phase = getPROJECTS.current_phase>
<cfset v_projtype = getPROJECTS.description>
<cfset v_fullname = getPROJECTS.fullname>
<cfset v_consultant = getPROJECTS.subproject_design_consultant>

<cfset v_afs3contract_type = getCONTRACTS.AFS3_contract_type>
<cfset v_afs3contract_no = getCONTRACTS.AFS3_contract_no

<cfset v_afs2contract_type = getCONTRACTS.AFS2_contract_type>
<cfset v_afs2contract_no = getCONTRACTS.AFS2_contract_no>


<cfquery datasource="#Application.rdsn#" name="InsertData">

INSERT INTO newtable
(
subproject_name,
subproject_id,
current_phase,
project_type
)
VALUES
(
'#v_subproject_name#',
#v_subproject_id#,
'#v_current_phase#',
'#v_projtype#'
)
</cfquery>
</cfloop>
</cfloop>

 

[cfStarlight]

Is there a reason you cannot combine the queries to avoid the nested looping?

What do you mean by "doesn't work"? What results do you get if you comment out the insert query, and just display the values instead?

 

[szeiss]

I'm trying to combine the queries, but so far haven't been successful in the results I want.

The above query repeats the first project it comes across and gives me:

getPROJECTS.project1 getCONTRACTS.contract1
getPROJECTS.project1 getCONTRACTS.contract2
getPROJECTS.project1 getCONTRACTS.contract3
getPROJECTS.project1 getCONTRACTS.contract4
getPROJECTS.project1 getCONTRACTS.contract5
getPROJECTS.project1 getCONTRACTS.contract6

 

[cfStarlight]

Okay. Well I would keep trying for a single query solution. Its usually better than nested loops, which obviously perform one db query for every row in the outer resultset.

You'll need to use a "from/to" loop instead a "cfquery" loop. Inside each loop use the #queryName.columnName[rowNumber]# syntax to extract the values. Personally I prefer it because it avoids abiguity that can occur in nested loops.

<!--- outer loop --->
<cfloop from="1" to="#getProjects.recordCount#" index="outerRow"> 
    <cfset someVariable = getProjects.someColumn[outerRow]>
    ... etc ...

Make sense?

 

[cfStarlight]

Perhaps its a copy/paste error, but the loop code looks wrong. It looks like it would run the getCONTRACTS query once for every row in "getPROJECTS". But it overwrites the getCONTRACTS every time it loops. That doesn't make sense.

<cfloop query="getPROJECTS">
   <cfquery name="getCONTRACTS" ...>
    SELECT DISTINCT  qTotal.AFS3_contract_type, ....
   </CFQUERY>
</cfloop>

<cfloop query="getCONTRACTS">
..

 

[philhege]

This problem begs for a single, JOIN query solution.

Consider this:

INSERT INTO MyResultTable
(MyField1,MyField2)

SELECT table1.MySourcefield, table2.MySourceField
FROM Table1
JOIN Table2 on MyMatchField = table1.MyOtherMatchField
WHERE Mycondition

This is a bulk insert statement, using the SELECT results as the source data. No loops necessary.

I'm fairly certain Oracle should be able to handle this type of syntax. (Sorry but I'm on the MS side of the database fence, so I can't provide the Oracle-specific solution.)

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[cfStarlight]

Phil is absolutely right. I don't know what I was thinking. Friday was more hectic than I thought :/