SQL : Eternal Error : Divide by zero Encountered : What to do ?

[t.georgy]

Hi, 
We are working on the transfer of our dashboards in Spotfire. 
We are using SQL connection in order to publish them and get automatic updates for end users. 

Everything is working fine except the division. Impossible to divide my "product" per car sold.

It's working fine if my output in an excel file to Spotfire but same data in an SQL query is not working in spotfire 

I get this : 

Data connection :An error occurred when executing a query in the external data source.

External error: Divide by zero error encountered.

Data connection: Microsoft SQL Server

How can I manage to make it work ? transform the "0" on what or which format in SQL ? 

In advance thank you, 

[David Boot-Olazabal]

Hi t.georgy,

Could you elaborate a bit more about the background situation?
And what does the transfer of dashboards in Spotfire mean?

Also, do I understand you correctly that if the data comes out of an Excel file, the division by zero in Spotfire gets executed, but when the data comes from SQL Server, it throws an error? That divide by zero, is that a calculated column in Spotfire? If so, how does the formula looks like? Do you have something built in for the divide by zero (such as a CASE statement)?

Kind regards,

David

[t.georgy]

Hi, 
Thank you for your reply, 

Yes, today when I load an excel file with exactly the same datas it's working.
I'm adding in spotfire two calculated columns to generate "New sales" and "products" columns. 

When I'm doing the division of "new sales" per "products" I'm getting my Ratio %


When I connect to my Microsoft SQL serveur and doing the same exercice : 
1/ calculate my "new sales"  (calculated column)

2/ Calculate my "products"

Divide my new sales / Products 

I'm getting an error : 

Data connection :An error occurred when executing a query in the external data source.

External error: Divide by zero error encountered.

Data connection: Microsoft SQL Server

[David Boot-Olazabal]

Hi,

Thanks for your explanation. So, the calculated column in Spotfire, the Ratio % one, is causing the error to occur?
In that case, the only thing I can come up with right (without seeing the data), is that one of the columns probably has a different type from SQL Server. Or do you use a conversion first to calculate the percentage?
What also could be, is that you may have left the data from SQL as external, whereas the Excel data might be imported.
If you can check if all your settings are the same, and the error still occurs, could you share a sample dataset from both sources?

Kind regards,

David

[t.georgy]

Thank you very much David, 

You are right, when I'm testing in Internal instead of External it's working. 
It's then link to External on Spotfire, do you know how I could deep dive on this ? 

In advance thank you, 

[David Boot-Olazabal]

Hi,

When you add data from database, they will be by default set to external. If you want them to be internal, which I would advise, you can do two things:
1. when adding data for the first time, pay attention to this screen and change external to internal:

When you click on the 'External' drop down, select 'Import':

2. When you already have added your data, you need to replace them with the same one, via the data canvas:

When clicking the Replace button, you will see an option to use the same data connection. When selecting that data connection, you see the tables that have been added to your analysis:

In my case, I added the WindNL_20240331 table as 'External' and want it to change to 'Import'. When you click OK, this will list all the available tables, and you need to select the one you want to replace:

And that brings you back to the same screen as the first one, the one where you can define if a table should be 'External' or 'Import':

In your case, as you already have the data table in the analysis file, I would suggest option 2. That should solve your problem.

Kind regards,

David

[t.georgy]

Thank you very much David,

Unfortunalty internal data will not allow me to publish my report as I do today for the others ones on "external" :(. 

I'd rather fix the issue on the External problem then to go internal. 

[Olivier Keugue Tadaa]

Hi Georgy

May we ask you to enable the DEBUG mode like this 👇 on your Analyst Client...

Then reproduce the issue and send us the log file.

We should be able to identify the SQL statement sent to your database and figure out how to fix it.

Have you also considered using a custom expression (for the ratio)  instead of dividing two calculated columns?

And finally, can you also send us the data table source information displayed here 👇 ("Data Table Properties" menu)?

 

... that would help