Re: Ignore Case
Date: Fri, 28 Feb 2003 08:09:44 +0100
Message-ID: <1046416160.618534_at_news.thyssen.com>
"Mark D Powell" <Mark.Powell_at_eds.com> schrieb im Newsbeitrag
news: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 --
Hi Mark,
thanks very much. Again learned a thing. Will that ever end? I don't hope so!
Greetings,
Guido
Received on Fri Feb 28 2003 - 08:09:44 CET