query/variable question

[tgriffin]

I am a little new at CF so bear with me...

I have a query name get_totals as
'Select x, y, sum(salary) as salary
from table
group by x, y
(There will only be three instances of y and 10 instances of x ever!)'

I need to then assign variables to parts of the results of this query. For example, I want to set cri1 = salary
where x = 'illinois' and y = 'chicago'. I will eventually have 30 different variables from this data set, since there are 10 x's and 3 y's.

What is the correct syntax to assign these values to variables using just the get_project query or do I need use seperate queries to generate each?

Thanks in advance!!

Traci

 

[GunJack]

Hi Traci,

I'm not sure I'm clear on everything. Is this how your table is structured?

x y salary
Illinois Chicago 30,000
Ohio Dayton 33,000
Michigan Lansing 40,000

I need to understand this before I can give you a better answer.
GJ

 

[tgriffin]

Yes, but the result set will be much larger than that. For example, there will be
X Y Salary
Illinois Chicago 30,000
Illinois Rockford 25,000
Illinois Decatur 18,000
Ohio Dayton 32,000
Ohio Colombus 29,000
Ohio Cleveland 30,000
Michigan Detroit 40,000
Michigan Lansing 50,000
Michigan Any City 45,000
and so on. There will only be 10 different states and 3 cities in each state. Each with a different Salary amount.

I have a query that returns the above information. I will need to display this information eventually. My question is: Do I need to to use seperate queries to get each individual instance of this result set or can I use the query that I used to get this result set and then use a for..next loop to output the data or to assign the individual state, city, salary combination to a variable.

 

[GunJack]

Hey Traci,

If you want to just display the results, then this will work.

<cfquery name=&quot;q1&quot; datasource=&quot;myDsn&quot;>
select * from table
order by x asc, y asc, salary desc
</cfquery>

<cfoutput query=&quot;q1&quot;>
#x# #y# #dollarformat(salary)#<br>
</cfoutput>

If you need to assign variables to each row, you can loop through the records and create dynamic variable names. This is a little more advanced so let me know if what I posted doesn't do what you need.

GJ

 

[tgriffin]

GJ,
Thanks...I will need to loop through the query then to to assign to a variable the salary amount now and output them later.
<cfset num = 1>
<cfloop>
<cfoutput query=&quot;get_data&quot;>
<cfset salary = &quot;cri&quot; & #num#>
<cfset num = num+1>
</cfloop>

Or should this be a for next loop?

 

[GunJack]

Hey Traci,

I think this is what you need.

<cfloop query=&quot;get_data&quot;>
<cfset &quot;cri#get_Data.currentrow#&quot; = get_Data.salary>
</cfloop>

I can't remember if it's currentrow or currentrecord so you may have to change that one. To reference the value of these dynamic variables, just use evaluate() like this.

<cfloop query=&quot;get_data&quot;>
<cfoutput>
The salary is #evaluate( &quot;cri#get_Data.currentrow#&quot#<p>
</cfoutput>
</cfloop>

If you don't need to loop through them, you can just reference them individually with <cfoutput>#cri1#, #cri2#</cfoutput>. Usually when you create dynamic variables, it's because you'll be looping through them so you'll need the evaluate function.

Hope this helps,
GJ

 

[tgriffin]

Thanks! That was exactly what I was looking for!!