Hey Larry why don't ya try adding "is not null" in there somewhere
/me ducks and runs 'cause I just added nothing to the conversation except to be a smartass.. plus I know dick about advanced PL/SQL -----Original Message----- From: Larry C. Lyons [mailto:[email protected]] Sent: Wednesday, November 11, 2009 3:59 PM To: cf-community Subject: Re: Weird error returned from a query. that may be the problem, there are a lot of rows where the column's value is empty. That gives me a lot to go on. many thanks, larry On Wed, Nov 11, 2009 at 2:36 PM, Michael Grant <[email protected]> wrote: > > Are there definately at least 3 chars in every row of the query? > > Could you try adding a where clause? Where length(region_abbrev) >= 3 > > Not sure if that's the right syntax for Oracle, but you get the idea. > > On Wed, Nov 11, 2009 at 12:48 PM, Larry C. Lyons <[email protected]>wrote: > >> >> H all, >> >> I'm getting a weird error instead of some query results. >> some background - its an oracle database and I'm trying to do a select >> based on the last 3 letters of a column >> >> here's my sql: >> select distinct SUBSTR(region_abbrev, -3, 3) >> from cls_regioninfo >> >> this works fine when I try it in SQL Developer but when i use it in a >> CF Query i get the following error. >> >> [Table (rows 70 columns SUBSTR(REGION_ABBREV,-3,3)): >> [SUBSTR(REGION_ABBREV,-3,3): coldfusion.sql.querycol...@e6e108] ] is >> not indexable by SUBSTR(REGION_ABBREV >> >> Any hints, suggestions or solutions would be very appreciated. >> >> larry >> >> -- >> Larry C. Lyons >> web: http://www.lyonsmorris.com/lyons >> LinkedIn: http://www.linkedin.com/in/larryclyons >> -- >> The real problem is not whether machines think but whether men do. >> - B. F. Skinner - >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:307701 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
