1

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}"
PaulMc
  • 109
  • 11

3 Answers3

2

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}"
Erubiel
  • 2,934
  • 14
  • 32
  • He wants to use `Action` (which is an alias) for the `CASE WHEN ...` statement. – Ivanka Todorova Aug 22 '18 at 22:58
  • i think both are valid answers, since no calculation/processing is done on alias Action – Erubiel Aug 22 '18 at 22:58
  • Yeah, that was my initial answer, but the question is old and OP recently edited it with a "simpler" version of what he was after. You can see it in the edits. – Ivanka Todorova Aug 22 '18 at 22:59
  • 1
    Ohh i understand now! anyways, for the new question both are right! – Erubiel Aug 22 '18 at 23:03
  • In a similar application we use Ignition's Power Table Component and it's Extension Function - configureEditor to allow a user to select from a Table Drop Down List "NULL", "Hold" or "Crash" and have it entered into the Database. See the following answer for this config. – PaulMc Aug 29 '19 at 01:07
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
Ivanka Todorova
  • 9,964
  • 16
  • 66
  • 103
0

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)
PaulMc
  • 109
  • 11