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

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

Don, I do not have a solution for you, but I would be interested to know what version of Oracle you are using and upon what platform you are running. Have you tried running a select using a dump() function to see what is stored in the column compared to the original table?

An alternate method of creating the table might be to create it in one step then use a seperate insert as select to populate it and see if queries against this work as expected. That would mean the problem is isolated to the create as select.

• Mark D Powell --