query of query and modifying column data
I normally don't use the query of query functionality built into ColdFusion. But, for the project I am currently working on (top secret as it is at this point in time), I need to use query of query.
So, in my first (real) query from the database I returned four columns with about 400 records. This is what an interface I am working through actually runs in the background. The query looked something like this:
FROM members
I needed to remove a few characters from the end of the "title" field because it had a ";1" or ";0" appended to the end of it (don't ask) based on whether or not they were a manager. So, I wrote a query of query to do this.
SELECT id, name, left(title,(character_length(title) - 2)) title, location
FROM qGetInitialMembers
WHERE location = 'MN'
</cfquery>
I get a really helpful error: Encountered "(. Incorrect Select Statement, Expecting a 'FROM', But Encountered '(' Instead, A Select Statement Should Have a 'FROM' Construct. What does that mean? I had no idea. Until I did some research. In what I read, it looks like in a query of query, you cannot alter the data of a column (cell). You can add to the query though. So, to return the title how I wanted it, I used this code:
SELECT id, name, title, location
FROM qGetInitialMembers
WHERE location = 'MN'
</cfquery>
<!--- create an array and populate it with values for the title --->
<cfset arrTitleFinal = arrayNew(1) />
<cfloop query="qGetMembers">
<cfset arrayAppend(arrTitleFinal,left(qGetMembers.title,(len(qGetMembers.title) - 2))) />
</cfloop>
<!--- add the new column to the query with values --->
<cfset queryAddColumn(qGetMembers,"title_final","VARCHAR",arrTitleFinal) />
So now the query of query qGetMembers returns four columns with a new one of title_final. You could even take this one step further (which I did) and do one more query of query to return the "title_final" column as "title" for consistency, but this works as is! This took me about four hours to figure out. Now that I know this, I definitely won't forget it, and I hope this helps someone out too, so they don't have to waste alot of time on the wonderful query of query.
SELECT id, name, left(title,(character_length(title) - 2)) title, location
FROM members
As I alluded to in my post, I am interfacing with a different technology written in Java. I just get the recordset from the Java program, in the format specified. I can't rewrite what that program gives me, so I have to do the query of query.
<cfoutput query="qGetMembers">
left(qGetMembers.title[CurrentRow],(len(qGetMembers.title[CurrentRow]) - 2))
</cfoutput>
easy peasy.....
Yup, I could have done it in the "view", but the application I am working on is in Model-Glue and in sticking with the MVC model, I thought that I should put that logic in the Gateway (model) of my application, otherwise, yes, you are correct, it would be really easy to do in the view!