| SQL "to_integer" conversion [message #370706] |
Fri, 21 January 2000 05:59  |
schnizer
Messages: 3
Registered: January 2000
|
Junior Member |
|
|
I have a special problem in creating a view:
An existing database column has the datatype varchar2(8). This column is filled with integer values; the datatype of this column cannot be changed.
In an 3'rd party client (via ODBC) i need this value as integer (that means number(8)) and not as double (that is number without scale).
The SQL-function to_number always returns number without scale; an user extended SQL function declared with return datatype integer also returns number without scale.
How can i force a number-expression to have the datatype number(8) ??
|
|
|
|
| Re: SQL "to_integer" conversion [message #370714 is a reply to message #370706] |
Fri, 21 January 2000 16:09  |
Mark E Kane
Messages: 7
Registered: January 2000
|
Junior Member |
|
|
Schnizer?
That's a tough one. I'll do some light research, but the best I can suggest is to use a procedure to convert it to a scalable number. It would not be so hard to write, but I'd suggest looking through some of the other web sites with files of PL/SQL tips so you would not have to reinvent the wheel.
If you do figure this one out, please post it or email me. My boss and I have a bet going on who will figure it out.
Mark
|
|
|
|
| Re: SQL "to_integer" conversion [message #370728 is a reply to message #370714] |
Mon, 24 January 2000 05:53 |
schnizer
Messages: 3
Registered: January 2000
|
Junior Member |
|
|
Now i've found a solution for this problem:
By defining an object type and a view on this type, subtypes of number can be defined as column types in the view.
-- SQL-Plus script to demonstrate this:
create or replace type int_type as object
(ival number(8)
);
/
create or replace view int_view
of int_type with object oid (ival)
as select to_number(cid) from char_tab;
describe int_view
Name Null? Type
--------------------- -------- ----
IVAL NUMBER(8)
The subtype is not implemented very sharp, since a conversion of a string with more than 8 digits does not raise an exception.
Possibly the external data type is more restrictive than the internal one, but no check is performed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
