Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
SQLFunction is formatting results? (Read 2769 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQLFunction is formatting results?
19.06.08 at 14:03:11
 
SQLFunction is formatting results?
 
Quote:
SQLFunction(...) seems to be formatting the result. This should be probably handled by Excel, e.g., in the case there's a customized decimal separator in Excel.

 
Answer:
I agree that this should be handled by Excel however to prevent problems SQL*XL does catch some problematic cases.
 
It all depends on the datatype of the returned value. This is passed to SQL*XL as a variant. If the data type is a vbDecimal value it will do a CDbl to try to convert the value. In very few cases there are problems with database servers sending the information as texts instead of as binary numbers. Then number formats become important.
Also null or empty values will be set to vbNullString.
On errors an error string will be put in the result value.
Back to top
 
« Last Edit: 19.06.08 at 14:07:21 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLFunction is formatting results?
Reply #1 - 21.07.08 at 15:37:14
 
Quote:
The problem still remains, the value is not properly converted to a number.
I think the decimal separator should be captured from Excel international settings and not in the Control Panel OS settings.
I have used a . (dot) as a decimal separator in Excel and in the control panel/system settings it is a , (comma).
The bellow function returns 123,456 and it should return 123.456

create or replace
function myfunction return number
as
begin return 123.456; end myfunction;

 
 
I tested the situation where the function result is not properly converted to a number. The difficulty with this seems to be with the parameters. If I specify a parameter to be of type other (undetermined) the driver I tested automatically sets the value as a string eventhough it is a numeric value. It is hard to overcome this as for a generic Excel function we never know the return type, we don't particularly want an additional conversion parameter to the function for this as it would complicate the use of the function.
 
I suggest you combine the SQL*XL function SQLFunction with the Excel function Value to convert to a number. The total Excel function will look like this: =Value( SQLFunction("f_myfunction") )
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1