How to Change Select Query Value to Text using MySQL?

Question

Take a look at the following query. How do you display Column 'Action' as text. If the result of 'Action' is LEQ 0 then dipslay the text "Crash" and if 'Action' is GRT 0 display the text "Hold"?

SELECT col1 AS Action
FROM vdk
WHERE t_stamp Between "{StartTime}" AND "{EndTime}"

Answer 1

Use CASE WHEN ... ELSE ... END and select from your set (query):

SELECT *, (CASE WHEN Action <= 0 THEN 'Crash' ELSE 'Hold' END) as ActionText
FROM (
    SELECT col1 AS Action
    FROM vdk
    WHERE t_stamp Between "{StartTime}" AND "{EndTime}"
) q

Answer 2

This application is similar to my first question and I thought it might help someone else down the the road. User can select from a Table's Drop Down List a set of options to enter a value into the Database.

Using Ignition's Power Table Component's Extension Function configureEditor with the following script. This script sets up the Drop Down List.

if colName == 'Action':
   return {options': [(0, 'Null'), (1, 'HOLD'), (2, 'CRASH')]}

Along with the same Power Table's Extension Function onCellEdited script. This script enters the selection as a value into the database.

#onCellEdited Upadte Query
row = rowIndex
col = colIndex
colName = colName
value = newValue

ndx = self.data.getValueAt(row,0)
query = "UPDATE vdk SET %s = ? WHERE ndx = ?" % colName
system.db.runPrepUpdate(query,[value,ndx],'history')
system.db.refresh(self.data)

Answer 3

Refactoring the answer above, since i don't see the necessity to add a query to an alias table. I think this should work the other answer should work too btw, but its a little more complicated query for no given reason.

SELECT (CASE WHEN col1 <= 0 THEN 'Crash' ELSE 'Hold' END) AS Action
FROM vdk
WHERE t_stamp Between "{StartTime}" AND "{EndTime}"