sql int interpretation problem

[de-tthomalla]

I am Running Suite 2.0.752.

I have text set up to pull data from a SQL server, which works fine. It pulls the most recent record that is the current weather conditions. However, today it is -4, and the database text on my page now shows 65532. This seems to be a 2s complement issue when the software is interpreting the data from SQL. The datatype in SQL is a smallint. Is there a workaround?

Tom

[de-tthomalla]

If I cast it to an int, it works properly:

cast([Temp] as int)

[Trent S]

Hello Tom,

Thank you for your enquiry.

This issue is caused by the way smallint is presented through ODBC.

You are correct in casting the temperature as an int to fix this issue.

For the benefit of other Forum users, let's go into some more detail:

This issue can be fixed inside of Noventri Suite by using an advanced select statement.
In Noventri Suite, get into the region that will be displaying your temperature, and then into the "Advanced" tab. Once you've connected to your database with the "Connect" button, click the "Select" button. Click on the "Advanced" radio button, and the use the following select statement in place of the default:

SELECT Your Temperature Column Name = CONVERT(int, Your Temperature Column Name) FROM [Your Table Name]

For example, let's say your table looks like this:



The default select statement is this:
SELECT * FROM TableName

With this select statement, the formatter [1:Temperature] would return: "65532" for a temperature of -4.

Our new select statement to correct this would look like this:
SELECT Temperature = CONVERT(int, Temperature), Place, Date FROM [Table_1]

Which with the same formatter of [1:Temperature] would return: "-4"

Another alternative: Simply convert your Temperature column's data type in your SQL database from smallint to int, and then refresh the database information inside of Noventri Suite.