I am trying to show a table of assets alarms with a filter for the asset ID, this works well but i would like to have the functionality to show all of the assets when an asset ID is not defined by the user.
(When the page is first loaded there will be no asset selected so i would like to show all)
this is my query currently, from googling i found the SQL function IIF which would suit this application but does not seem to be recognized by ignition as it doesnt change colours when i type it.
select DateTime as "Time of Trip", tP_Alarms.Description, tP_Alarms.AssetID, tL_Alarms.OperatorID, tL_Alarms.State, DurationSeconds from tL_Alarms
inner join tP_Alarms on tP_Alarms.ID = tL_Alarms.AlarmID
where ( tL_Alarms.State = :StateQ or tL_Alarms.State = 1 )
--and ( datetime > :datee and datetime <= :datee2 )
and tP_Alarms.AssetID = (select IIF (:AssID = BETWEEN 0 and 20, ":AssID", "( tP_Alarms.AssetID > 0 and tP_Alarms.AssetID < 20 )") )
order by datetime desc
The error i get here is “The index 2 is out of range.” If anybody has a solution or work around to my problem i would be very grateful.