Re: Outer Joint Error

From: Dante <dnotari_at_my-deja.com>
Date: Thu, 03 Jun 1999 09:24:15 GMT
Message-ID: <7j5hjq$b8v$1@nnrp1.deja.com>

Pete,
I realize it is not what you asked for, but just an observation.

If you use rtrim() the optimizer will not use any index (except if you use functionbased index in Oracle 8.1), which will cause a low performance.

I recommend to use the datatype VARCHAR2 for your text columns (in case you don't have) and trim the values when you are inserting them (eg. in a trigger). You can change the dataype by ALTERing the TABLE.

Updating the textcolumns to set them to RTRIM(textcolumn).

The benefits are that you need less space on your FS and improved performance.

Regards
Dante

In article <3756107A.8E494573_at_2xtreme.net>, PMG <pete_g_at_2xtreme.net> wrote:
> I get an error msg, "ORA-00933: SQL command not properly ended" when I
> try to execute the following outer join. Can somebody explain why
using
> a function in the where clause prevents the use of an outer join? Or
> better yet, how to get around this problem.
>
> select something
> from tableA, tableB
> where rtrim(a.name) = rtrim(b.name) (+)
>
> TIA
>
> Pete
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 03 1999 - 04:24:15 CDT