SQLFunction is formatting results?


Welcome, Guest. Please Login.

ExcelLock: Locking and securing your valuable Excel spreadsheets

encOffice: Protect your Excel file easy and safe

LIT Software Forums › SQL*XL › General Usage and Feature Troubleshooting

‹ Previous topic | Next topic ›

Pages: 1

SQLFunction is formatting results? (Read 3770 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.

« Last Edit: 19.06.08 at 14:07:21 by Gerrit-Jan Linker »

Gerrit-Jan Linker
Linker IT Software

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") )

Gerrit-Jan Linker
Linker IT Software

IP Logged

Pages: 1

LIT Software Forums › SQL*XL › General Usage and Feature Troubleshooting

‹ Previous topic | Next topic ›

Copyright (C) 1995-2007 Linker IT Software BV. All Rights Reserved. Oracle is a registered trademark of Oracle corporation. Excel and Office are registered trademarks of Microsoft corporation. Other names appearing on the site may be trademarks of their respective owners. Software, files, documents, articles and other material are provided "as is" and without warranties as to performance or mechantability or any other warranties whether expressed or implied. No warranty of fitness for a particular purpose is offered.
sitemap