Re: Ignore Case

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 27 Feb 2003 10:17:31 -0800
Message-ID: <2687bb95.0302271017.26268474_at_posting.google.com>

"Guido Konsolke" <Guido.Konsolke_at_triaton.com> wrote in message news:<1046329814.525941_at_news.thyssen.com>...
> "Mark D Powell" <Mark.Powell_at_eds.com> schrieb im Newsbeitrag
> news:2687bb95.0302260622.201d08cf_at_posting.google.com...
> (snipped OP)
> >
> > Mike, I suspect you are getting a syntax error but since you do not
> > provide any basic information on what version of Oracle you are using,
> > what tool you are using, or post the actual query results here is my
> > WAG:
> >
> > ver 8.1.7.4 from sqlplus
> > UT1> select *
> > 2 from marktest
> > 3 where upper(mpowel01.marktest.fld4) like upper('two')
> > 4 /
> > where upper(mpowel01.marktest.fld4) like upper('two')
> > *
> > ERROR at line 3:
> > ORA-00904: invalid column name
> >
> >
> > UT1> c /mpowel01.//
> > 3* where upper(marktest.fld4) like upper('two')
> > UT1> /
> >
> > no rows selected
> >
> > Remove the owner from the where clause as this is not legal Oracle
> > syntax though it might be legal for some tool sets, but without more
> > information I do not see how anyone is going to be able to help.
> >
> > HTH -- Mark D Powell --
>
> Hi Mark,
>
> sorry, I can't comfirm that. In my 8.1.7.0 with SQL*PLUS:
> 08:02:48 po92>select count(*)
> 08:03:09 2 from iaw.t_wache_umlauf_h
> 08:03:24 3 where upper(iaw.t_wache_umlauf_h.stamm_gattung_tx) like
> upper('%sammp%');
>
> COUNT(*)
> ---------
> 9536
>
> real: 18167
> 08:04:41 po92>
>
> I tried this as user 'iaw' (owner of the table) and as another one.
> I'd thank you if you could shine some light on this case.
>
> Greetings,
> Guido

Guido, it would appear that if you prefix the table_name in the from clause then Oracle lets you prefix it in the where clause, but not otherwise:
UT1> select * from mpowel01.marktest
  2 where upper(mpowel01.marktest.fld4) like upper('%no%')   3 /

FLD1 FLD2 FLD3 FLD4
---------- ---------- --------- ----------

six                 6 10-JAN-03 no 6

UT1> 1
  1* select * from mpowel01.marktest
UT1> c /mpowel01./
  1* select * from marktest
UT1> l
  1 select * from marktest
  2* where upper(mpowel01.marktest.fld4) like upper('%no%') UT1> /
where upper(mpowel01.marktest.fld4) like upper('%no%')

                              *

ERROR at line 2:
ORA-00904: invalid column name

Interesting. You would'not think that this should matter, but obviously it does. -- Mark D Powell -- Received on Thu Feb 27 2003 - 19:17:31 CET