can you not set a variable that's in the same scope as a query structure

[Nick Way]

ok so i got a query

<cfquery datasource="mydb" name="note_this_name" >
select columnone, columntwo
from tbl_mytable
</cfquery>

i then do this

<cfset note_this_name.not_a_column_name = "i_will_never_be_able_to_use_this_variable" >

then i do this

<cfoutput>#note_this_name.not_a_column_name#</cfoutput>

and i get this error " Element not_a_column_name is undefined in note_this_name"

How on earth can this be? am i going mad?

[Newsgroup_User]

happysailingdude wrote:
>
> How on earth can this be? am i going mad?
>

Well, if you really just want to add to the query column, personally I
would use the query functions such as
queryAddColumn(query,'column','dataType',arrayOfValues) and|or
querySetCell(query,'column',row,value).

I would suspect that your issue stems from a query record set being a
complex variable and that your simple <cfset...> does not properly
extend the variable.

[paross1]

I don't think that you can do that, but you might try something like this:

cfquery datasource="mydb" name="note_this_name" >
select columnone, columntwo, NULL AS not_a_column_name
from tbl_mytable
</cfquery>

Create the "extra" column in the query, then set a value later using cfset.

<cfset note_this_name.not_a_column_name = "i_will_never_be_able_to_use_this_variable" >

it will now have a value

<cfoutput>#note_this_name.not_a_column_name#</cfoutput>

Phil

[Nick Way]

hi Ian and Phil

thanks very much indeed for your replies.

I didn't implement Ian's suggestion, as whilst i can see where your coming from and it might well work - Phil's suggestion looked like the easiest one to try first and sure enough it did the trick so i didn't go any further than that.

Thanks very much to you both for taking the time to reply - is much appreciated.

PS Phil: just for the benefit of anyone else trying to solve the same problem that i had; do you think that solution would work on all databases (ours is MySQL - but would it work on any DB do you think?)

cheers

[Newsgroup_User]

happysailingdude wrote:
>
> PS Phil: just for the benefit of anyone else trying to solve the same problem
> that i had; do you think that solution would work on all databases (ours is
> MySQL - but would it work on any DB do you think?)
>
> cheers
>


Phil and my solutions solve slightly different problems but with a great
deal of over lap.

Phil's solution is standard SQL and as such should work on any database
management system of which I am aware.

Ian

[paross1]

FYI, just a note of caution. When using the solution that I posted, if your query returns more than one row, and you use CFSET after your CFQUERY to set the pseudo column with a value, only one row will have a value set and the remaining rows will still be NULL.

If you do something like this instead, and set the column to a value using a constant within the query:

cfquery datasource="mydb" name="note_this_name" >
select columnone, columntwo,
'i_will_never_be_able_to_use_this_variable' AS not_a_column_name
from tbl_mytable
</cfquery>

then every row returned would contain the value of 'i_will_never_be_able_to_use_this_variable' in the not_a_column_name column.

Phil

[-__cfSearching__-]

happysailingdude wrote:

<cfset note_this_name.not_a_column_name = "i_will_never_be_able_to_use_this_variable" >
...
and i get this error " Element not_a_column_name is undefined in note_this_name"
How on earth can this be? am i going mad?

Though this was mentioned already, I think it is worth clarifying. You cannot implicitly add a column to a query that way. A query is not the same type of object as a CF structure. So AFAIK you cannot apply that particular technique to queries. To add a column your must use one of the options Ian and Phil mentioned.