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:307682 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
