Help with ${SQL:Select ... syntax containing multiplication, please

What's the correct way to format this SQL statement? I'm attempting to convert a celsius value to fahrenheit to include it in the alert message and NPM Event Log. Thank you!!

${SQL:Select CASE ${SensorUnit} WHEN 'Celsius' THEN (${SensorValue} * 9 / 5) + 32 ELSE 0 END}

I get this error .... MACRO SQL ERROR - incorrect syntax near '*'

Does it think I'm trying to do a SELECT * statement?

btw, I'm not sure if the SensorUnit will contain 'Celsius'

search fields: alert, asterisk, multiply, multiplication

Find more posts tagged with

temperature

conversion

Alert

celsius

multiply

multiplication

asterisk

Accepted answers

marunderwood

All of these ended up working ....

(Fahrenheit: ${SQL:Select CASE WHEN '${SensorUnit}' = 'Degrees Celsius' THEN (CONVERT(int,'${SensorValue}') *(9/5)+32) ELSE 0 END})

Fahrenheit: ${SQL:Select CASE WHEN '${SensorUnit}' = 'Degrees Celsius' THEN (CONVERT(int,'${SensorValue}') *(9/5)+32) ELSE 0 END}

Fahrenheit: ${SQL:Select CASE '${SensorUnit}' WHEN 'Degrees Celsius' THEN (CONVERT(int,'${SensorValue}') *(9/5)+32) ELSE 0 END}

I think the trick was to treat the sensor value as a text field (since the value was actually UNKNOWN and the sensor unit was blank) by putting the quotes around it & converting it to an integer when it was expected to have a numeric value.

Thanks @zackm for your input. Especially the table/view name apm_hardwaresensoralertdata which I viewed to verify the sensorunit name. btw, the table name was not needed.

All comments

zackm

Try this:

${SQL:selectcase when ${SensorUnit} = 'degrees celsius'then (${SensorValue} *(9/5)+32)else 0 endfrom apm_hardwaresensoralertdatawhere hardwaresensorid = ___________}

You'll have to find the last variable that goes in the '__________' (I don't have a demo site in front of me at the moment, sorry)

The trick is that you need to run a full SQL query within your variable. What you had posted was missing the table name and such.