January 16, 2008 at 4:57 am
Hi,
Not sure how much of response this will illicit, seeing as this is the only topic here, but here goes...
I have a view in an Oracle 10g database called WIP_WORK_ORDERS. Here it is:
CREATE OR REPLACE VIEW WIP_WORK_ORDERS AS
select
a.wo_no,
c.customer_no,
c.agreement_id,
a.reference_no,
UPPER(a.address1) as ADDRESS1,
a.reg_date as LOGGED_DATE,
c.real_f_date as COMPLETION_DATE,
TO_NUMBER(b.TOTAL_COST) as TOTAL_COST,
from active_separate a
left join WORK_ORDER_COST b on a.wo_no = b.wo_no
left join WORK_ORDER c on a.wo_no = c.wo_no;
I've left out the WHERE clause because I don't want to confuse the issue.
I also have a SQL Server 2k database, dbMYDB, and have added the Oracle database as a linked server (OracleDB) using the Oracle OLE DB Provider (Oracle 10g Release 2 ODAC).
I then run the code below:
SELECT * INTO WIP_WORK_ORDERS FROM OPENQUERY(OracleDB, 'SELECT * FROM schema.WIP_WORK_ORDERS')
My question is - why, when this code is executed, do I end up with a table where the TOTAL_COST column is an NVARCHAR, instead of a FLOAT, MONEY or other numerical data type?
I've wrapped the TOTAL_COST column in a "TO_NUMBER" function in the Oracle view just to make sure, but I still end up with an NVARCHAR. Is this something to do with the driver?
January 16, 2008 at 11:15 am
Hello,
You have to specify the format mask of the to_number function.
See
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm
At second thought, name the columns. Select column1,column... FROM OPENQUERY (... SELECT ... column1,....) in case the columnorder would change.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy