SQL Where (usage)

the SQL WHERE clause (1=1) "trick"

When you program Magic with a SQL backend, you'll often want to use eDevelopers SQL Where clause.
Often you then have programs which build this WHERE clause "dynamically" (concatenatenation of a string gated by conditions) like in:

(:X and @:X notation left away for the sake of clearity)

A = A & 'AND NAME="' & v.SearchName % '"' (IF v.SearchName <> )
A = A & 'AND LOCATION="' & v.City % '"' (IF v.City <> AND v.SearchForCities)
...

Code like this requires you to add special handling for the case where you do NOT want to restrict the search because the user did not specify any search criteria.

This you can do either by adding additional logic which handles the fact that the first clause you add to your SQL WHERE string cannot start with "AND" or "OR" or ....
you simply init your string with "1=1".

Whatever you concatenate to the SQL WHERE clause now, you can safely start it with "AND" or "OR".

If you do not add anything to the WHERE clause, Magic will generate SQL like:
"SELECT ... FROM GREAT_CLUBS WHERE 1=1"

If you specified search criteria Magic will generate SQL like:
"SELECT ... FROM GREAT_CLUBS WHERE 1=1 AND COUNTRY="Germany" AND NAME="1860 München""

This has no impact on performance or whatever. A RDBMS optimizes this away and a RDBMS does always have an optimizer.

Note: The example given above is somewhat constructed. A perfect replacement for this would be eDevelopers CndRange()∞ function which basically allows to do the same. There's however things which you cannot use in a Range expression and which do belong in the SQL WHERE clause. Samples for this would be correlated subqueries or constructs with EXISTS or IN. Moving too much "logic" into the SQL WHERE (or Magic WHERE clause) clause you better avoid. Think about using views instead or in addition.