I have a query on my page that returns all the data from a shipping table by date.
SELECT *
FROM shipping
WHERE ({{ !variable13.value }} OR ship_date = {{ variable13.value }}) AND cancel = FALSE AND deleted = FALSE
ORDER BY ship_date, load_num, dealer
I have been asked to add a way to search the data by dealer. Unfortunately, after spending numerous hours on it, I cannot figure out how to add that to the query.
Basically I am setting a variable (variable13) to the date because the user can select different dates. Now I want to add dealer (select119.value) to the mix, but if the user enters a dealer in the search box, I just want to return all records for that dealer. I'm not worried about date.
I want the table to display data from today when it is first loaded, which is was doing. I attempted to implement this query:
IF EXISTS (SELECT * FROM shipping WHERE dealer = {{ select119.value }})
BEGIN
SELECT * FROM shipping WHERE dealer = {{ select119.value }} AND cancel = FALSE AND deleted = FALSE
END
ELSE
BEGIN
SELECT * FROM shipping WHERE ship_date = {{ variable13.value }} AND cancel = FALSE AND deleted = FALSE
END
and I keep getting "syntax error at or near 'IF'".
I hope I have explained this well enough that someone can help with the correct SQL syntax. Thank you.
SELECT *
FROM shipping
WHERE
(
{{ select119.value }} -- If dealer is selected, ignore date filter
OR ship_date = {{ variable13.value }} -- Otherwise, apply date filter
OR {{ !variable13.value }} -- Also include all records if no date is selected
)
AND ({{ !select119.value }} OR dealer = {{ select119.value }}) -- Apply dealer filter only if selected
AND cancel = FALSE
AND deleted = FALSE
ORDER BY ship_date, load_num, dealer;
Mmm, it's tough to troubleshoot without having access to the data, try the below:
SELECT *
FROM shipping
WHERE
(
({{ select119.value }} IS NOT NULL)
OR ({{ select119.value }} IS NULL AND ship_date = {{ variable13.value }})
)
AND ({{ !select119.value }} OR dealer = {{ select119.value }}) -- Apply dealer filter only if selected
AND cancel = FALSE
AND deleted = FALSE
ORDER BY ship_date, load_num, dealer;
I changed the query to this and it is partially running:
SELECT *
FROM shipping
WHERE
(
{{ !variable19.value }} -- If dealer is selected, ignore date filter
OR ship_date = {{ variable13.value }} -- Otherwise, apply date filter
OR {{ !variable13.value }} -- Also include all records if no date is selected
)
AND ({{ !variable19.value }} OR dealer = {{ variable19.value }}) -- Apply dealer filter only if selected
AND cancel = FALSE
AND deleted = FALSE
ORDER BY ship_date, load_num, dealer
I set the format for variable13 to be "MMM DD, YYYY" which is how it is in the database, but it is returning every single record now.
If I select a dealer, it returns those records just fine. That's what I meant by it's partially running.
So, you mean that when you select a dealer, then the dates are not applying?? If so, that is correct and how the query is set up.
You could apply the date filter outside from the query, either on the query transformer, or in the data source of whatever you're using (table or list view)
Yes, when I select a dealer, the dates are not applying, but I still wanted the data from today to populate when someone first comes to the page. Instead of returning today's data, it is returning all data. And if I try to select another date, it still returns all data.
