How can I set a variable from a computed value in a query?

[WarmFront]

I'm trying to use a computed columns value from a query to set a variable.
Here is the query:
    <cfquery name="qhiRole_OwnIHSArea" datasource="#at_datasource#">
    SELECT max (fk_JobUser_Role)
    from Job_DelgtAreaRole
    WHERE IHSUID = #qJobUser.IHSUID# and IHSAreaAssignedID = #qJobUserArea.JOBIHSArea_ID#
    </cfquery>

I dumped the query and got the value I wanted as "computed_column_1.

Then I tried this: <cfset userRole = #qhiRole_OwnIHSArea.computed_column_1# />
The error message said "computed_column_1" was undefined.
I tried changing the query to "Select max (fk_JobUser_Role) as theRole
but that was "undefined", too.

Any way to grab the result of the query to set a variable afterwards?

Thanks.

[ilssac]

    <cfquery name="qhiRole_OwnIHSArea" datasource="#at_datasource#">
    SELECT max(fk_JobUser_Role) AS aNiceName
    from Job_DelgtAreaRole
    WHERE IHSUID = #qJobUser.IHSUID# and IHSAreaAssignedID = #qJobUserArea.JOBIHSArea_ID#
    </cfquery>
<cfoutput query="qhiRole_OwnIHSArea">
  #aNiceName#
</cfoutput>

I hear good things about the books "Teach Yourself SQL in 10 minutes" and "Database Design for Meer Mortals".

[ilssac]

Ok, ignore the previous post... I did jumped before understanding the entire post.

[ilssac]

Do you really have a space between the max and the opening parentheses?

SELECT max (fk_JobUser_Role)
          ^
          space

That looks very unusal to me.

Otherwise I do not know what the problem is, because your query looks very normal and I do stuff like this all the time.

[ilssac]

Ok, I think I need a nap before I try more replies....

<cfset userRole = #qhiRole_OwnIHSArea.computed_column_1# />
OR
<cfset userRole = #qhiRole_OwnIHSArea.theRole# />

Neither of those should work outside of a <cfoutput query=""...> or <cfloop query=""...> loop block.  These loop block syntax provide shortcuts to the full reference of a record set.  If you are trying to fully refrence a record set outside of one you must provide all the parts in the form of either queryName.columnName[rowNumber] OR queryName["columnName"][rowNumber].  So those lines probably need to look like..

<cfset userRole = #qhiRole_OwnIHSArea.computed_column_1[1]# />
OR
<cfset userRole = #qhiRole_OwnIHSArea.theRole[1]# />

[insuractive]

Have you tried aliasing your computed column like this:

<cfquery name="qMyQuery">

SELECT max(fieldA) 'fieldA_Max'

FROM myTable

</cfquery>

<cfoutput>#qMyQuery.fieldA_Max#</cfoutput>

[WarmFront]

Thanks for helping me.

I don't have any trouble getting a record from the query, but when I try this:

<cfoutput>
43 :           #qHiRole_OwnISHArea.theRole#
44 :           </cfoutput>

The error message is still "theRole" is not defined in qHiRole_OwnIHSArea.

Dumping the query result gives this:

query - Top 1 of 1 Rows
	THEROLE
1	7

which is what I want.

Also, strangely (to me, anyway), I need to remove the conditional I tested with to even get the error message.
The conditional is:

<cfif IsDefined("qHiRole_OwnISHArea") and qHiRole_OwnISHArea.recordcount is "1">
     <cfdump var="#qhiRole_OwnIHSArea#" />
          <cfoutput>
          #qHiRole_OwnISHArea.theRole#
          </cfoutput> 

I appreciate your assistance. If this doesn't work soon, I'll just return all the values and then pick the max one from a list, I guess.

[ilssac]

Are you doing something that puts strange spaces in your code when you are posting it in this forum?  Or is the forum doing it itself.

Otherwise you have really strange spacing in some of your variable and function code.

[chandra12345]

Which database u r using?If you are using sql server use @@identity to get the latest db record.

Thanks,

[Dan_Bracuk]

The easiest way is to give your computed column an alias in your query.  This was alluded to in another answer.  That one include quotes around the alias name, something I have never found necessary.

[WarmFront]

For whatever reason, I wasn't able to check the recordcount of the query result unless I executed the code immediately after the query ran.

By the time I figured that out, I had fixed the problem by returning all values from the query and then using ArrayMax to choose the largest

value.

Thanks for everyone's help. Alias would have been a solution, too.