Potential Gotchas

Joining Strings

When building custom URLs for pages or images, it's common that people will try to use the default SQL string addition operator (+).  But the problem is that the default (+) operator will only work on strings.  If you pass [string] + [integer] you'll receive an error because any other data types will not be automatically converted.

An easy way we avoid this is to use the CONCAT function.  The concatenation function automatically converts all element types to strings before joining.  Instead of:

-- [NodeID] is an integer
'/Web/Url/Images/' + [NodeID] + '.gif' AS [Image]

use:

-- [NodeID] is an integer
CONCAT('/Web/Url/Images/', [NodeID], '.gif') AS [Image]

Converting Data Types

By design, the API will return the data type of the original element.  If you query for an element that's an integer, you will get an integer.  This makes perfect sense, until we start factoring in arithmetic.

Integers to Decimal

However, there are times when we want to change the data types.  One common example is doing averages of an element which is an integer.

Let's assume you have some historical response time numbers for a specific entity.

-- Fake query for discussion
SELECT Caption AS [Node Caption]       -- string
     , ResponseTime AS [Response Time] -- integer
FROM [Orion].[SomeRandomEntity]
WHERE [ObservationTimestamp] >= ADDMINUTE( -15, GETUTCDATE() )
  AND [Caption] = 'SERVER1'

 You would expect to get something like the below table.

The unit for response times is milliseconds, so they are stored as integers. 

-- Fake query for discussion
SELECT Caption AS [Node Caption]                    -- string
     , AVG(ResponseTime) AS [Average Response Time] -- ????
FROM [Orion].[SomeRandomEntity]
WHERE [ObservationTimestamp] >= ADDMINUTE( -15, GETUTCDATE() )
  AND [Caption] = 'SERVER1'
GROUP BY Caption

If you were to query for the average across this aggregate set, you would receive 12 as your result.  Doing some quick math, you'd see that the actual result should be closer to 12.57.  Why isn't the API capable of doing simple math?  It's doing it fine; it's just making some erroneous assumptions.

When you perform mathematical operations, the API looks to the "source" data type and expects the same in the result.  In simplest terms, it's seeing that you are querying across integers and therefore the result should be an integer.  It's a logical assumption that works 90% of the time but doesn't work when we start dividing or averaging those same integers.

There's an easy way to combat this problem.  Multiple the individual elements by 1.0.  The decimal point is critical here.

-- Fake query for discussion
SELECT Caption AS [Node Caption]                          -- string
     , AVG(1.0 * ResponseTime) AS [Average Response Time] -- force to a decimal
FROM [Orion].[SomeRandomEntity]
WHERE [ObservationTimestamp] >= ADDMINUTE( -15, GETUTCDATE() )
  AND [Caption] = 'SERVER1'
GROUP BY Caption

The API knows that an integer multiplied by a decimal number will result in a decimal number and takes this into account.  By telling the API that you want each integer multiplied by 1.0, the resultant data type will be a decimal value.

Placement of the multiplication is important because we need to alter the types at the beginning of the call and not against the result of the arithmetic.

-- Fake query for discussion
SELECT Caption AS [Node Caption]                               -- string
     , AVG(1.0 * ResponseTime) AS [Average Response Time_Good] -- Do this
     , 1.0 * AVG(ResponseTime) AS [Average Response Time_Bad]  -- Not this
FROM [Orion].[SomeRandomEntity]
WHERE [ObservationTimestamp] >= ADDMINUTE( -15, GETUTCDATE() )
  AND [Caption] = 'SERVER1'
GROUP BY Caption

Strings to Decimals

if you have a data type that looks like a number, but is stored as a string, the same trick works.

SELECT TOP 1 '11' AS [This is a string]
     , 11 AS [This is an integer]
     , 11.0 AS [This is a decimal]
     , '11' * 1.0 AS [This is a decimal]
FROM Orion.RandomEntity

Why would numerical information ever be stored as a string? Although it's not best practice from a data organizational practice, there are several places where it's come up in the past.

Having this trick in your back pocket could prove useful.