Query help, show selected assets or if none selected show all

Ignition
#1

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.

#2

This doesn’t sound like a SQL error.

Can you show the code where this query is being called, and provide more detail on the error?

1 Like
#3

Honestly have no idea what that error means or how it went away but i came to the solution ill post it here incase anybody stumbles across it with a similar problem

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 tP_Alarms.AssetID = (IIF(:AssID = -1, tP_Alarms.AssetID, :AssID))
order by datetime desc