Coldfusion Array for Database query

Question

I'm trying to query a database, with the cfml(sql) statement below, but I want to be able to get results for each stat_instance from an array, the last line contains the loop, which I think should generate a query per value from the array

select 
    <cfif monthCrossover>
        statsMinEnd2 + 
    </cfif> 
    stats_min as time_stamp, 
    <cfif structKeyExists(avgThis, arguments.key)>
        avg
    <cfelse>
        sum
    </cfif>
    (stats_value) as stats_value 
from stats_kv_table_201207 
where 
    <cfif dayStart == dayEnd>
        stats_day = <cfqueryparam value="#dayStart#">
    <cfelse> 
        stats_day >= <cfqueryparam value="#dayStart#"> 
        and stats_day <= <cfqueryparam value="#dayEnd#">
    </cfif>
    and stats_min >= <cfqueryparam value="#statsMinStart1#"> and stats_min <= <cfqueryparam value="#statsMinEnd1#">
    and stats_component = <cfqueryparam value="#arguments.component#">
    <cfset instanceList = ListToArray(arguments.instance)>
    <cfif ArrayLen(instanceList) EQ 1>
        and stats_instance = <cfqueryparam value="#arguments.instance#">
    </cfif>
    <cfif ArrayLen(instanceList) GT 1>
        and stats_instance IN (<cfqueryparam value="#arguments.instance#" list="yes"/>)
    </cfif>
    <cfif ArrayLen(instanceList) GT 1 and ArrayContains(instanceList, "multi")>
        <cfloop index = "i" from="1" to="#arrayLen(instanceList)#"  array =#instanceList#> 
            <cfset stats_instance = <cfqueryparam value= #instanceList[i]#>
        </cfloop>
    </cfif>

azawaza · Accepted Answer · 2012-10-26 05:59:26Z

1

If I understand your question correctly, you probably need to use GROUP BY clause in your query to group your sum/avg values by stats_instance. Something like this:

SELECT stats_instance, ...
FROM ...
WHERE
... 
<cfif len(trim(arguments.instance))>
AND stats_instance IN (<cfqueryparam value="#arguments.instance#" list="yes"/>)
</cfif>
GROUP BY stats_instance
ORDER BY stats_instance

NOTE: syntax may be different depending on your database engine.

answered Oct 26, 2012 at 5:59

azawaza

3,0941 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

deetu Over a year ago

thanks for the suggestion, what im trying to achieve is to generate data per stat_instance, lets say i have just one instance, it uses that instance in the WHERE statement and generate just one, but when i have an array of instances, i want the sql statement to generate data for each stat_instance it sees in the loop of arrays

John · Accepted Answer · 2012-10-25 18:48:06Z

0

Is there something wrong with doing it this way?

<cfset myArr = ArrayNew(1) />
<cfloop array="myOtherArr" index="index">
    <cfquery name="myQuery" datasource="myDataSource">...</cfquery>
    <cfset ArrayAppend(myArr, myQuery.value) />
</cfloop>

answered Oct 25, 2012 at 18:48

John

13.8k7 gold badges53 silver badges104 bronze badges

1 Comment

deetu Over a year ago

thanks for the suggestion, what im trying to achieve is to generate data per stat_instance, lets say i have just one instance, it uses that instance in the WHERE statement and generate just one, but when i have an array of instances, i want the sql statement to generate data for each stat_instance it sees in the loop of arrays

Collectives™ on Stack Overflow

Coldfusion Array for Database query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related