simple cfoutput question. (stumpped)

Hi All,

I have a very simple <cfoutput> query question.

I have two independent queries:
<cfquery name="clients" datasource="#request.dsn#">
Select id as Clid, clientName from clients_temp
where id = 1
</cfquery>


<cfquery datasource="#request.dsn#" name="agency">
Select id as agid, agencyName from agency_temp
</cfquery>


The first query retrieves 1 record
Second query retrieves 2 records.

I then run a loop.


<cfoutput query="agency">
#agid#
<cfloop query="clients">
#agid# <br>
</cfloop>
</cfoutput>
Agency query retieves values for the id field called agid.
I wanna use the value of the outer query inside the loop of the second query. But agid within the second loop/query gives me a "variable undefined error".

But if I set to a temp variable, it wrks.
Eg:
<cfoutput query="agency">
<cfset tempVar = #agid#> #agid#
<cfloop query="clients">
#tempvar# <br>
</cfloop>
</cfoutput>

I can also use valuelist to run a similar scenario.
<cfoutput query="agency">
<cfloop list="#valuelist(clients.clid)#" index="counter" >
#agid# <br>
</cfloop>
</cfoutput>

Why cant I get the first scenario to work.

Why cant I access the outer query variable in the inner query ?

Any suggestions or leads are appreciated.

tx,
rigante

First, always prefix your query variables with the query name (ie #clients.id# if the query was named clients.

Also, I'm not understanding why you'd want to return all of the agencies...isn't the idea to only return the agency that the client is assocaited with? And if that is the case, is there a reason why you aren't doing this in one query using a join?

Does your outer loop actually loop the same information as does the inner loop? Or is the repition of #agid# in both loops, just for testing/our purposes?

I tried scoping it to agency.agid

Say the values of agid in the agency table is 1,2 . (Two records)

But putting agency.id shows me only the same value, though the loop runs twice
Eg:
1,1 when its sorted asc
and 2,2 when sorted desc


On another note:
When you do <cfoutput>#agency.agid#</cfoutput>. It would return only one value. The last record in most of the cases.


what happens to agid when its accessed in the inner loop ?
It has no problems when its outside the inner loop.

A friend asked me this question, I didnt believe him till I created a temp db table and wrote the above queries.

It might take about 5 mins to set up an this table schema. Its too much to ask. But can u give it a shot?

Cause I dont understand why I cant access the current value of an outer query in an inner querry? without putting it to a variable ?

tx

bastion,



The display of agid inside and outside the inner loop is for testing purposes.

Both the tables have id. So I have renamed those to

For the clients table
Select id as Clid,

For the agency table
Select id as agid,

Therotically speaking, this what I thought should be happening.

Values for agid = 1,2 (2 records)
Values for aDESC = google, yahoo
Values for clid = 1 (1 record)


First round of the loop
agid = 1 --- aDESC= google --- Clid= 1

Second round of the loop
agid = 2 --- aDESC= yahoo --- Clid= 1


The point is the outer loop(Agency query) runs twice and the inner loop runs once.
Please dont look into the logic part now.

I just heard from a reliable source that "You need to set to a variable." before using it in the inner loop.

<cfoutput query="x">
<cfset Tempvar = QryVar>
<cfoutput query="y">
#TempVar#
</cfoutput>
</cfoutput>

dang,

I dont whether to call it a feature or a bug

What version of CF are you using?

After some testing, it does look to me like you need to copy the var into a temp variable in order for the inner loop to see it properly. I had thought this was fixed in CFMX 6.1 but I guess it has not. I'll try it on 7 to see if it is still an issue.

kiteless,

Tx for testing though.

I am running this on CFMX 6.1

kiteless,

Tx for testing though.

I am running this on CFMX 6.1