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

Don Chambers wrote:
>
> 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

Well, I suspect that there is a misprint somewhere. When I write TRIM(col = 100) with my 8.1.7 system, I get a 'missing parenthesis' error.

However, granting that this is incomplete (I am also wondering what caused you to try TRIM in the first place) I will offer some speculation as to what might be happening.

In my experience, whenever something 'impossible' like this appears, it turns out to be due to an implicit type conversion, and once one follows all the rules one-at-a-time the 'impossible' turns out to not only be possible, but to be exactly what is documented. TRIM acts on a character string. So, whatever is inside the parenthesis will have to be of type character. If you have numbers involved, they will have to be converted to character before they can be trimmed.

Of course, Oracle Support should have thought of all this.

This problem also reminds me of some of the rounding problems I ran into during an earlier life as an actuary. Of course, rounding cannot be a problem with NUMBER(15) columns.

I am real curious to see what the precise result is when you run your query on the original table.

Please post the solution when you finally get it; I am curious.

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

Go Wings!!!!!!!