6 Replies Latest reply on Apr 22, 2008 4:59 PM by -==cfSearching==-

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

    happysailingdude Level 1
      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?
        • 1. Re: can you not set a variable that's in the same scope as a   query   structure
          Level 7
          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.

          • 2. Re: can you not set a variable that's in the same scope as a query structure
            paross1 Level 2
            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
            • 3. Re: can you not set a variable that's in the same scope as a query structure
              happysailingdude Level 1
              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
              • 4. Re: can you not set a variable that's in the same scope   as a query   structure
                Level 7
                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
                • 5. Re: can you not set a variable that's in the same scope   as a query   structure
                  paross1 Level 2
                  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
                  • 6. Re: can you not set a variable that's in the same scope   as a query   structure
                    -==cfSearching==- Level 4
                    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.