Re: Stange problem on Number column. TRIM fixes but should not

Could be an an index problem. Using a function causes the optimizer to not use an index (possibly any index, or maybe just one index or another-it depends). Of course, you'll probably tell me that you don't have any indexes on these columns...

"Don Chambers" <dchamber_at_mindspring.com> wrote in message news:r69u5u4tsf54t60o0is135ghjf52vv0m2m_at_4ax.com...
> I have a table that includes 2 Number(15) columns. One called base_id
> and the other system_id. If I execute the following query I get no
> rows returned:
> SELECT base_id
> FROM testTable
> WHERE base_id = 100
> AND system_id = 200;
>
> If I put a TRIM around EITHER of the conditions in the where clause I
> get 34 rows back ( ex. TRIM(base_id = 100) ). It does not matter
> which field gets the trim.
>
> If I use only one field, for example:
> .....
> WHERE base_id = 100
>
> I get no rows unless I use the TRIM. With the TRIM I get rows
> returned.
>
> These are numeric columns so I don't see how TRIM could do anything to
> them. Even if it did why can I put it on EITHER field and get the
> same result?
>
> I created the table from another table as:
> CREAT TABLE testTable
> AS (SELECT * FROM anotherTable WHERE ...)
>
> The original table does NOT have this problem. I exported the problem
> table and imported into 2 other instances and I still have this
> problem.
> I've submitted this to Oracle tech support, sending them an export of
> my table as well as the query. They have duplicated the problem but
> have not yet offered a solution.
>
> What could cause this?
> How can I fix it?
>
> Thanks,
> Don
> dchamber_at_mindspring.com
Received on Tue Feb 05 2002 - 09:25:36 CST