Trying to Add a Column To My Query

[scripter73]

Hi,

I'm trying to add a column to an existing query with the following:

<cfset column1 = QueryAddColumn(newdata,&quot;boodoo&quot;,array1)>

where
<cfset array1 = ArrayNew(1)>
<cfloop index=&quot;counter&quot; from=&quot;1&quot; to=#newdata.recordcount#>
<cfset array1[counter] = 0>
<cfoutput>#array1[counter]#</cfoutput><br>
</cfloop>


newdata is the existing query, &quot;boodoo&quot; is the new column name

I KEEP getting the following message: The column name (boodoo) that you have specified already exists in this query. Column names must be unique.

It doesn't matter what I change the name to, Cold Fusion will say it isn't unique. I'm not using duplicate names. I change it to something silly and I still get the same message.

I heard you can't use QueryAddColumn on a cached query. How can I check that? Maybe since I'm debugging alot, its cached?

Any help, ANYONE?

Thanks in advance,
scripter73


Change Your Thinking, Change Your Life.

 

[calista]

Did you use QueryNew() to create your query? Calista :-X
Jedi Knight,
Champion of the Force

 

[scripter73]

Hi calista,

No. I already have an existing query. I've been working on it and I got the columns added. But now, I'm having a problem updating every row cell of the field that I added.

Here's a snippet of my code. It updates the cell of the first row, and then it doesn't update anything else. Is QuerySetCell a one-time process? It should work for every row. I'm putting the function in a loop, and I've verified the loop is executing the appropriate number of times.

Any help is greatly appreciated.

NEWDATA is an existing query with the added fields FastFood, FastCuisine. Now I want to update the FastFood column with values taken from another existing query.


<cfoutput query=&quot;newdata&quot;>
<cfset branch = Mid(newdata.wnew_policy_alpha,1,2)>
<cfset company = Mid(newdata.wnew_policy_alpha,3,2)>
<cfset product = Mid(newdata.wnew_policy_alpha,5,2)>
<cfloop query=&quot;getivr&quot;>
<cfif (#getivr.vr00_branch# eq #branch# and #getivr.vr00_product# eq #product# and #getivr.vr00_company# eq #company#)>
<cfset querySetCell(newdata,&quot;FastFood&quot;,&quot;#getivr.vr00_orig_issued_tot#&quot;,newdata.currentrow)>
</cfif>
</cfloop>
</cfoutput>


CF doesn't give an error, my records simply aren't updated when I display the newdata query again.


Thanks,
scripter73
Change Your Thinking, Change Your Life.

 

[calista]

Have you tried simply outputing the data you want to insert, just to make sure you're getting the results you expect? I've made the mistake of thinking one step was giving the wrong data, when the problem was actually further upstream.

No offense intended, you're probably already thought of that. Calista :-X
Jedi Knight,
Champion of the Force

 

[scripter73]

Hey, calista,

I'm going to back track as you suggested.

I already have Query1 established. I'm creating Query2. Instead of waiting at the end to compare partial codes, I will just combine those partial codes into one field.

However, my Query2 field is not getting setup properly.

How do I query a table, and then create another field in that query based on 3 fields? I'm sorry. I think I'm rattling on now. I'll just show you my code so far:



<cfquery name=&quot;getivr&quot; datasource=&quot;#session.dsn#&quot;>
select vr00_branch,
vr00_company,
vr00_product,
vr00_policy_number,
vr00_uprate_reasons,
vr00_orig_issued_tot
from ARIVR
</cfquery>

<!--- create an array for IVR --->
<CFSET IVRArray = ArrayNew(1)>
<cfloop index=&quot;counter&quot; from=&quot;1&quot; to=#getivr.recordcount#>
<cfset IVRArray[counter] = &quot;#getivr.vr00_branch##getivr.vr00_company##getivr.vr00_product#&quot;>
</cfloop>
<!--- add the column to the query --->
<CFSET nColumnNumber = QueryAddColumn(getivr, &quot;polalpha&quot;,
IVRarray)>



All I want to do is attach another field (call it anything) that contains the first three concatenated fields. Maybe then I can take Query1 and Query2 and merge them somehow.

Thanks.


Change Your Thinking, Change Your Life.

 

[scripter73]

Hi calista,

I decided to just do two separate <CFQUERY> commands and then intertwine them into a loop.

Sort of like...

<cfloop query=&quot;Q1&quot;>
<cfloop query=&quot;Q2&quot;>
1) do processing where elements from Q1 match Q2
2) add all needed elements from both queries into one array
</cfloop>
</cfloop>



I don't know why I'm having such a problem with this one report, I've done alot harder, but I'll keep you posted on the outcome.

Thanks again,
scripter73


Change Your Thinking, Change Your Life.