Hello, I'm working on generating a dynamic SQL query where I need to include multiple categories, each having its own independent field filters. My query should look something like this:
SELECT * FROM sales..dsweek2023 WHERE category = 'Washing Machines' AND field1 IN ('Option1', 'Option2') UNION ALL
SELECT * FROM sales..dsweek2023 WHERE category = 'Air Conditioners' AND field2 IN ('Option3', 'Option4') UNION ALL
...
However, I'm facing two main issues:
1.Single Category Output: My generated SQL query is only showing the last category in the list, omitting all previous ones.
2.Synchronized Field Filters: The field filters for each category are not independent; changing the field filter dropdown for one category affects the others.
Could anyone provide some insights or suggestions on how to resolve these issues?Thank you!Feel free to modify the question as per your requirements.
Thank you !
Hello Wang,
As Dorine explained, you need to append new SQL statement to previous existing one (using union all) in case you have more than one category selected.
Also you are using two different lists which are not connected "List_DynamicDropdownList" and "Cate_List_CategoryDynamicList". So as per Dorine answer you can include List_DynamicDropdownList as attribute of Cate_List_CategoryDynamicList list.
Kindly find attached oml, I did some changes and try to implement what you need to generate correct SQL statement.
Hello Mostafa,
Thank you ever so much for your insightful and precise response - it perfectly addressed my issue! I am profoundly grateful for your continuous support and assistance. I apologize for the late reply, as I took some time to fully understand and apply the solution you provided. Thank you once again for your patience and incredible support!
1. i'm assuming you want to have the complete sql for all categories in GeneratedSQL2 (maybe give it a better name ?) All you are doing now is in each iteration give it the value of GeneratedSQL1 + literal 'union all) I would expect you do something like
SQL2 = SQL2 + (If not the first, " UNION ALL ", "") + SQL on each iteration
2. You use List_DynamicDropdownList , but you have no link / relation between List_DynamicDropdownList and category, so how could your code know which field is for which category ? I would expect either a list with filters, where each item also refers to what category it is a filter for, or to have a list of filters as a child in the list of categories
Dorine
Thank you Dorine,you are right ,I lost relation between List_DynamicDropdownList and category,Thank you very much!