It's redundant if your where clause asks for a length >= 3. Length of a null value is < 3.
On Wed, Nov 11, 2009 at 4:57 PM, Larry C. Lyons <larrycly...@gmail.com>wrote: > > yes I'll have to include that. > > On Wed, Nov 11, 2009 at 4:19 PM, Scott Stewart > <sstwebwo...@bellsouth.net> wrote: > > > > 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:larrycly...@gmail.com] > > 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 <mgr...@modus.bz> 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 > > <larrycly...@gmail.com>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:307713 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
