ColdFusion query output?

Question

so I have to output query column values based on some other column values. For this I used cfloop to check each value from my query column and then set the values. My code looks like this:

<cfloop query="myQuery">
    <cfif status EQ "ABC" OR status EQ "DEF">
        <cfif trim(exitStatus) EQ ''>
            <cfset col8val = #col8val#>
        <cfelse>
            <cfset col8val = #col8val# A>
        </cfif>
    </cfif>
</cfloop>

So I'm wondering what I should use to store my results in one place that way I do not have to loop, I want simple just to dump my result set after this cfloop above. I can not use array or structure because both will require additional looping to get results. I also tried to do this in SQL but I could not combine two CASE statements from two different columns. If anyone can help with this problem please let me know.

Answer 1

You can do a case statement from two different columns, but maybe you just don't know how. I would do further research into whatever flavor of SQL you are using to see how it's done. In SQL Server you could do something like this:

SELECT CASE WHEN status IN ('ABC', 'DEF') AND COALESCE(exitStatus,'') = ''THEN col8val 
            ELSE col8val + 'A' 
       END AS TheNewValue
FROM   TableName
WHERE  ....

Another option is to physically edit the query object and this post by Ben explains how to do that: Ask Ben: Updating Columns In An Existing ColdFusion Query Object.

Your exact question however:

So I'm wondering what I should use to store my results in one place that way I do not have to loop, I want simple just to dump my result set after this cfloop above. I can not use array or structure because both will require additional looping to get results.

I don't totally understand your question. You want to "store your results in one place" but you don't want to use an array or structure. Well, if you can't use an array or structure, how on earth do you think you can store data that isn't primitive? It also isn't clear how you intend to use the data. There are a few functions that others have written that will quite efficiently turn a query into arrays or structs or lists, etc. But if you need to check and alter any of the data, your only option is to write a loop, use someone's function and modify it to do the checks you need to alter the data, or alter your SQL. You might be able to get creative with QoQ but, honestly, if you are looking into that, you might as well do it in the original query.