Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
query add row at top of query
Author: Michael Grant
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#336007
I know I'm a little late to the game here, but wouldn't the easier way be
just to union an explicitly set row?
It's a LOT less code and lets the db take the load.
<cfquery name="GetParks" datasource="cfdocexamples">
SELECT PARKNAME, REGION, STATE, cORDER = 2
FROM Parks
Where REGION = 'Southeast Region'
UNION
SELECT TOP 1
PARKNAME = 'myName',
REGION = 'myRegion',
STATE = 'myState',
cORDER = 1
FROM Parks
ORDER BY cORDER, ParkName, State
</cfquery>
----- Excess quoted text cut - see Original Post for more -----
Author: Richard White
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#335932
thanks to all posts, and thanks Ria, i like this method :)
----- Excess quoted text cut - see Original Post for more -----
> name="GetParks" datasource="cfdocexamples"
> cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">
> SELECT PARKNAME, REGION, STATE
> FROM Parks
> Where REGION = 'Southeast Region'
----- Excess quoted text cut - see Original Post for more -----
Author: Ria Ragam
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#335913
You can refer to this link to appendQuery
http://www.bennadel.com/blog/114-ColdFusion-QueryAppend-qOne-qTwo-.htm
I guess you should go with method one as of my understanding. Posting example
here.
appendQuery.cfm
<cfquery
name="GetParks" datasource="cfdocexamples"
cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">
SELECT PARKNAME, REGION, STATE
FROM Parks
Where REGION = 'Southeast Region'
ORDER BY ParkName, State
</cfquery>
<cfset testQuery =
queryNew("PARKNAME,REGION,STATE","varchar,varchar,varchar")>
<cfset queryAddrow(testQuery,1)>
<cfset test1 = createObject('component','appendQuery')>
<cfset resultQuery = test1.QueryAppend(testQuery,GetParks)>
<cfdump var="#resultQuery#">
<cfabort>
<cfset test1 = createObject('component','appendQuery')>
<cfset resultQuery = test1.QueryAppend2(testQuery,GetParks)>
<cfdump var="#resultQuery#">
appendQuery.cfc
<cffunction name="QueryAppend" access="public" returntype="query"
output="false"
hint="This takes two queries and appends the second one to the first one. Returns
the resultant third query.">
<!--- Define arguments. --->
<cfargument name="QueryOne" type="query" required="true" />
<cfargument name="QueryTwo" type="query" required="true" />
<cfargument name="UnionAll" type="boolean" required="false" default="true"
/>
<!--- Define the local scope. --->
<cfset var LOCAL = StructNew() />
<!--- Append the second to the first. Do this by unioning the two queries.
--->
<cfquery name="LOCAL.NewQuery" dbtype="query">
<!--- Select all from the first query. --->
(
SELECT
*
FROM
ARGUMENTS.QueryOne
)
<!--- Union the two queries together. --->
UNION
<!---
Check to see if we are going to care about duplicates. If we don't
expect duplicates then just union all.
--->
<cfif ARGUMENTS.UnionAll>
ALL
</cfif>
<!--- Select all from the second query. --->
(
SELECT
*
FROM
ARGUMENTS.QueryTwo
)
</cfquery>
<!--- Return the new query. --->
<cfreturn LOCAL.NewQuery />
</cffunction>
----- Excess quoted text cut - see Original Post for more -----
Author: Leigh
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#335911
Probably, though you can easily verify it yourself.
One option is using a UNION. Either in a QoQ (or your original database query).
Assign a static sort value to each statement. Then ORDER BY that value to produce
the desired ordering.
SELECT 0 AS SortValue, 'Some Value' ColumnA, 'Some Value' ColumnB
UNION
SELECT 1 AS SortValue, ColumnA, ColumnB
FROM yourQueryName
ORDER BY SortValue
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#335912
> i have a query which has sort criteria applied at mysql level.
>
> i want to add a row at the top of the query after mysql has finished with
it.
>
> If i use the queryaddrow method it adds the row to the bottom of the query
>
> is there anyway to get it to add it as the first item?
You could add the new row in the SQL itself using a UNION statement,
couldn't you?
Otherwise, you could add it using queryAddRow, querySetCell, etc, but
you'd have to figure out how to get things in the order that you want
them. You could create a new query, add the row to the new query, then
loop through the old query and add each row to the new query. Or you
could use queryAddRow and querySetCell with the existing query, but
have a sortable field for all rows including the new row, then use
query of query to sort the query again.
But I'd go with doing this in your SQL if I were you.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/
Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or
onsite.
Author: Richard White
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#335909
thanks, although would that not just overwrite the data in row 1?
----- Excess quoted text cut - see Original Post for more -----
Author: Ria Ragam
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#335908
<cfquery
name="GetParks" datasource="cfdocexamples"
cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">
SELECT PARKNAME, REGION, STATE
FROM Parks
Where REGION = 'Southeast Region'
ORDER BY ParkName, State
</cfquery>
<cfset temp = querysetcell(GetParks, "PARKNAME", " ", 1)>
<cfset temp = querysetcell(GetParks, "REGION", " ", 1)>
<cfset temp = querysetcell(GetParks, "STATE", " ", 1)>
----- Excess quoted text cut - see Original Post for more -----
Author: Richard White
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61728#335906
hi
i have a query which has sort criteria applied at mysql level.
i want to add a row at the top of the query after mysql has finished with it.
If i use the queryaddrow method it adds the row to the bottom of the query
is there anyway to get it to add it as the first item?
thanks
|
May 24, 2012
|
Latest Fusion Authority Articles
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
