What I have done with these wierd QofQ issues is to wrap TO_CHAR() around my attributes in the original SQL. (using Oracle here) The JDBC drivers then handle it all as text, but CF numerics still work on the attributes, som emajic casting in CF, eh? Sorting maybe an issue, hasn't bothered me yet. This is very useful when working with date attributes and nulls, like when you try to union two Qs with QofQ.
DK On 7/23/05, James Holmes <[EMAIL PROTECTED]> wrote: > For me it probably wouldn't matter often, but some people may want a > QoQ like this: > > SELECT * > FROM SomeQuery > WHERE MyNumber IS NOT NULL > ORDER BY MyNumber > > to work properly, whereas if all of the nulls are now zeros it > breaks. A two step query would do it I guess: > > 1) Do this on the original data (where CF got the number column wrong > and thought it was a string, e.g. because of a null in the first row > of the number column) > > SELECT * > FROM SomeQuery > WHERE MyNumber IS NOT NULL > > which would filter out the undesired rows > > 2) Recast the above query with the QueryCast UDF I'm writing and then > do the final sort > > SELECT * > FROM TheAboveQuery > ORDER BY MyNumber > > I'm happy to continue the work for those who still find it useful, but > with the caveat that null numbers will be converted to 0. > > On 7/24/05, Raymond Camden <[EMAIL PROTECTED]> wrote: > > I'm confused as to why converting the numbers to 0 isn't helpful. For > > your data, is there really a (practical) difference between null and > > 0? (They may be - but it might be a good idea to double check.) > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212699 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
