SQL query to Oracle - Only getting integer part of numeric columns

2014 7 25
0 个回答
更新时间：2014 7 28
5 次查看（30 天）

0 个投票

I have just bought the database toolbox so I am not very familiar with this. I want to retrieve data from an Oracle database. Each row in my dataset contains both string columns and numeric columns. I have set the DataReturnFormat to 'table'.
The columns that contain strings look fine in my result table but the numeric columns only have the integer part, i.e. 101.37 becomes 101, 0.0425 becomes 0 etc.
Running the same query in AQT gives me the correct data. (That is the way I used to do it - either AQT and export to excel and xlsread from Matlab or running the query in VBA + xlsread)
There is probably a simple change in settings that fixes this but right now this eludes me.
Regards,
Niclas

6 个评论
显示 4更早的评论 隐藏 4更早的评论

Geoff Hayes
Geoff Hayes 2014-7-25
Niclas - What is the code that you are using to build the query and extract the data? What happens if you use cellarray (the default) instead of table?
Niclas Engberg
Niclas Engberg 2014-7-25
If I use cellarray it looks pretty much the same. Just the integer part for the numeric columns. The code I use to build the query looks like this
sqlQueryBuilder=['SELECT BON.ISSUEID, BON.COUPON, BON1.ISSUEID, BON1.ACTIVEPRICE, '... 'BON1.ACTIVETYPE, BON1.ACTIVEFREQ, BON1.ISSUESPECIFIC, '... 'BON1.CREDITSPREAD, BON1.LASTUPDATE, ISS.ISSUEID, '... 'ISS.CURRENCY, ISS.MOODYRATING, ISS.SPRATING, '... 'ISS.CLEANDIRTYFLAG, ISS.ISSUERCODE, DIS.ISSUEID, '... 'DIS.MATURITYDATEADJ, BON.FRNMARGIN, DIS.PRICEYIELDCALCBASIS '... 'FROM SA.BONDISSUES BON, '... 'MGR.BONDISSUEMATRIXPOINT BON1, '... 'SA.ISSUE ISS, '... 'SA.DISCOUNTISSUE DIS '... 'WHERE BON.ISSUEID=BON1.ISSUEID '... 'AND to_char(ISS.CURRENCY)=''',ccy,''''... 'AND to_char(ISS.SPRATING)<>''',nr,''''... 'AND BON.ISSUEID=ISS.ISSUEID '... 'AND BON.ISSUEID=DIS.ISSUEID '... 'AND to_char(BON1.LASTUPDATE,''yyyy-mm-dd'') >=''', datestr(coaDate,dateFormat ),''''... 'ORDER BY BON.ISSUEID ' ];
formattedSQLQuery = sqlQueryBuilder;
end
Geoff Hayes
Geoff Hayes 2014-7-25
Which fields should be retuned as real (non-integer) numbers?
Try the following - build your query selecting just one of these fields that should be returned as a real number. Use the same from and where conditions. Run the query and observe the results - is the data for that one field returned as an integer or not?
Niclas Engberg
Niclas Engberg 2014-7-28
I have tried just picking one (numerical) column and I still only get the integer part.
The query
'SELECT to_number(BON.COUPON) FROM SA.BONDISSUES BON ORDER BY BON.ISSUEID '
produces a column of zeros. (All the coupons being less than 1)
Even setting the dataReturnFormat to numeric still only gives me zeros.
Niclas Engberg
Niclas Engberg 2014-7-28
Issue solved. It seems the database uses decimal komma "," while Matlab only recognizes decimal point ".". Using to_char(xxx,'9990.99999'), where xxx is whatever numeric column I want, did the trick. Or at least I get strings that can be converted to the correct numerical values.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Database Toolbox 的更多信息

产品

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by