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

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

[Geoff Hayes]

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]

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]

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]

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]

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.

[Geoff Hayes]

Awesome that you figured it out!