SQL where clause (with condition) help in Ignition

Question

I currently have a SQL query that I am running in Ignition. It works OK if I select a specific area. The issue is that I would like to add an "ALL" to it. I would like to be able to ignore area as being a condition if it has the "ALL" selection.

I am not certain if this could just be handled with a SQL query. I am running a query on the dropdown to populate it with the areas.

This is my code:

SELECT *
FROM jobrecord
where
    jobopendate >= '{Root Container.Group 5.Group 3.Popup Calendar.date}' AND
    jobopendate <= '{Root Container.Group 5.Group 3.Popup Calendar 1.date}' AND
    area = '{Root Container.Group 5.Group 3.Dropdown 9.selectedLabel}'
ORDER BY jobnumber

score 1 · Answer 1 · answered Aug 24 '20 at 17:23

You need to use an or where clause condition.

If you pass @Area as all this script will return all areas. If you pass a specific value then it will filter to that area value.

declare @Area nvarchar(max) = 'someArea' 

SELECT *
FROM jobrecord
where
    (@Area = 'ALL' or (jobopendate >= '{Root Container.Group 5.Group 3.Popup Calendar.date}' AND
    jobopendate <= '{Root Container.Group 5.Group 3.Popup Calendar 1.date}' AND
    area = @Area))
ORDER BY jobnumber

score 1 · Answer 2 · answered Aug 24 '20 at 18:12

Thank You!

My final code:

declare @Area nvarchar(max) = '{Root Container.Group 5.Group 3.Dropdown 9.selectedLabel}' 

SELECT *
FROM jobrecord
where
    (@Area = 'ALL' and (jobopendate >= '{Root Container.Group 5.Group 3.Popup Calendar.date}' AND
    jobopendate <= '{Root Container.Group 5.Group 3.Popup Calendar 1.date}') or
    (area = @Area and jobopendate >= '{Root Container.Group 5.Group 3.Popup Calendar.date}' 
    and jobopendate <= '{Root Container.Group 5.Group 3.Popup Calendar 1.date}'))
ORDER BY jobnumber

SQL where clause (with condition) help in Ignition

2 Answers2