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

The logic doesn't quite add up. I thought that TRIM function was related to a collection (e.g pl/sql table, arrays etc).

Could you supply some test data and a DESC of your table?

M

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 - 07:29:44 CST