New post
Article
· Apr 25, 2020 2m read

Static WHERE Conditions

#Other

The typical WHERE condition in SQL relates mostly to some content of the rows you work on.
So it needs to be calculated and checked for each row you access.
Differently (and that's why I named it STATIC) is a WHERE condition that is independent of the rows you access.


The nice thing about it:
It is accessed and evaluated only once before anything else happens.
You could interpret it as a kind of main switch to your table.
In the past, this was often used as a workaround for simple SQL access rules.
A more tricky use is the combination with a SQLprocedure like this very simple example:

Class User.Hack
{ ClassMethod chk(any As %String) As %Boolean [ SqlName = mychk, SqlProc ]
{   set if=$get(any)#2 ;; just a placeholder for a complex condition
    break              ;; for interactive examination
    quit if
  } }

and to get all lines and check only once (!!) you run:

SELECT id,name,Home_State from sample.person where home_State %startswith 'A'
       and MYCHK('rcc')=1  

differently, dynamic use (with ID)  it will be evaluated for each line
- line with odd ID 

SELECT id,name,Home_State from sample.person where home_State %startswith 'A'
       and MYCHK(id)=1 

- lines with even IDs

SELECT id,name,Home_State from sample.person where home_State %startswith 'A'
       and MYCHK(id)=0 


The important point is you have a backdoor to your SQLstatement to pass your specific parameters into a query
before any access to your data.
I have used this already inside notes for various replies.
A further article will demonstrate a practical use case.

Discussion (2)1
Log in or sign up to continue
Benjamin De Boe · Apr 27, 2020

If you need a programmatic entry point for dealing with complex filter conditions, take a look at %SQL.AbstractFind. It allows you to invoke ObjectScript code to populate bitmaps based on (any number of) user-provided arguments through a %FIND predicate. It is built on by iFind and this older (but worthwhile) community article on custom spatial indices, although in both cases it works hand-in-hand with a custom index (using %FunctionalIndex). That isn't required, but usually the flip side of the same use case coin.

2 0