ColdFusion Insert a Column Into Cfquery

Problem:How to insert a new column into an existing cfqury and set its value to a computed value? For example, if there is a cfquery selecting book title, price and quantity from a bookstore table, and I want to insert a column called “discount” into the cfquery and set the discount value based on the current row’s price and quantity.

Solution: Use QueryAddColumn to add a column into the query, then use querySetCell to set the column value.

Code Example

<cfquery name="inventory" datasource="myDB">
select title, price, quantity from bookstore
</cfquery>

<cfset QueryAddColumn(inventory,"discount", "Decimal",ArrayNew(1))>

<cfloop query="inventory">
<cfset discount = 0>
<cfset currentItemPrice = inventory.price[inventory.currentRow]>
<cfset currentItemQty = inventory.quantity[inventory.currentRow]>
<cfif currentItemPrice gt 30 and currentItemQty gt 50>
<cfset discount=0.8>
</cfif>
<cfset querySetCell(inventory,"discount", discount,inventory.currentRow)>
</cfloop>

<cfoutput query="inventory">
#inventory.title#, #inventory.price#, #inventory.quantity#, #inventory.discount#
</cfoutput>

Advertisements
This entry was posted in Coldfusion, Database and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s