Participating Frequently

Question

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

Forum|Forum|17 years ago
May 6, 2009
7 replies
1710 views

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.

Advanced techniques

This topic has been closed for replies.

W

WarmFrontAuthor

Participating Frequently

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.

D

Dan_Bracuk

Inspiring

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.

C

chandra12345

Inspiring

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

Thanks,

ilssac

Inspiring

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]# />

I

insuractive

Inspiring

Have you tried aliasing your computed column like this:

SELECT max(fieldA) 'fieldA_Max'

FROM myTable

</cfquery>

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

W

WarmFrontAuthor

Participating Frequently

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

Inspiring

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

Inspiring

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

ilssac

Inspiring

    <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".

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded