Sorry about the confusion earlier, I wasn't clear with my suggestions. 1. Like pointed out above, the default constraint works when you do an INSERT INTO statement block into your tables and leave out the column with the default constraint on it. When you do that, if the default constraint is in place, then the column will get 99 instead of NULL, in your case.
2. Setting a default constraint, however, does NOT automatically update your existing column and change NULL into 99. So, you need to run a UPDATE script on the table to change the NULL values to 99. 3. Then after that, you can do the following in your CF template <cfif LEN((TRIM(form["sortORD_#myIDX#"]))> link_sort = <cfqueryparam value="#Form["sortORD_#myIDX#"]#" cfsqltype="CF_SQL_VARCHAR"> </cfif> That way, only rows explicitly updated by users will have a "real" sort_by value, all others have 99. On Fri, Sep 12, 2008 at 6:23 PM, Dawson, Michael <[EMAIL PROTECTED]>wrote: > If your database supports it, you can also use CASE to change your sort > order. > > SELECT > col1 > ,col2 > CASE WHEN col1 IS NULL THEN 99 > ELSE col1 > END AS sortOrder > FROM > table > ORDER BY > sortOrder > > I have found that this comes in very handy when trying to re-arrange the > order of data that may not make sense sorted alphabetically. > > Mike > > -----Original Message----- > From: Les Mizzell [mailto:[EMAIL PROTECTED] > Sent: Friday, September 12, 2008 3:59 PM > To: CF-Talk > Subject: Re: A Little Confusion on CFQUERYPARAM and Nulls... > > Qing Xia wrote:> then simply do: > > <cfif LEN((TRIM(form["sortORD_#myIDX#"]))> > > link_sort = <cfqueryparam value="#Form["sortORD_#myIDX#"]#" > > cfsqltype="CF_SQL_VARCHAR"> > > </cfif> > > Yea, was hoping to avoid all those extra cfif statements and keep it a > little neater. Oh well!! > > The reason for the default "99" (or whatever) was to give the sorting > column a high number value so record with an actual sort number > (1,2,3,4,5...) would come up first. This leads me to my *other* option - > go ahead an insert the NULL and then use coalesce on the output query to > keep those pesky records with no sort number at the bottom of the pile. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312481 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
