how to put dynamic filter clause in Embedded SQL
Hi All,
I am facing some issues. I am having a SQL query that is executed using %SQL.Statement ExecDirect() method. Now I want to convert it into Embedded SQL using &SQL().
But where clause is dynamic in my case and It may contain 3-4 clauses.
Product version: IRIS 2023.2
Discussion (2)1
Comments
The difference between embedded SQL (&sql() )and Dynamic (%ResultSet or %SQL.Statement) is that embedded is static and compiled in the code. You can only use parameters (preceded by colon) in the Where and Into Clause, but not change the SQL itself. You could write a bunch of If statements to call different &sql( ... ) code, depending the conditions, but if these combinations are to numerous you better use dynamic SQL.
If you wish to have a conditional filter, then you can use it this way
SELECT * FROM SomeTable
WHERE (:FilterName = '' OR Name LIKE :FilterName)
AND (:FilterValue1 = '' OR Value1 LIKE :FilterValue1)
AND (:FilterValue2 = '' OR Value2 LIKE :FilterValue2)