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

<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>
<cfset querySetCell(inventory,"discount", discount,inventory.currentRow)>

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

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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s