how to put dynamic filter clause in Embedded SQL

Question

Question

Gautam Rishi · Jul 26, 2023

#SQL #InterSystems IRIS #InterSystems IRIS for Health

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

Log in or sign up to continue

score 0 · Answer 1 · 2023-07-26T06:48:01-04:00

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.

score 0 · Answer 2 · 2023-07-26T09:12:54-04:00

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)