Most recent edit on 2007-04-14 20:54:54 by AndreasSedlmeier
Additions:
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.
Deletions:
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.
Edited on 2007-04-14 20:45:19 by AndreasSedlmeier
Additions:
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.
Edited on 2007-04-13 12:43:49 by FrankVanHerreweghe [edited a typo]
Additions:
If you specified search criteria Magic will generate SQL like:
Deletions:
If you specified seacrh criteria Magic will generate SQL like:
Edited on 2007-04-11 15:01:00 by AndreasSedlmeier
Additions:
SQL Where (usage)
the SQL WHERE clause (1=1) "trick"
(:X and @:X notation left away for the sake of clearity)
Deletions:
SQL Where (usage and tricks)
Edited on 2007-04-11 14:50:57 by AndreasSedlmeier
Additions:
"SELECT ... FROM GREAT_CLUBS WHERE 1=1"
If you specified seacrh criteria Magic will generate SQL like:
"SELECT ... FROM GREAT_CLUBS WHERE 1=1 AND COUNTRY="Germany" AND NAME="1860 München""
Deletions:
"SELECT ... FROM CLIENT WHERE 1=1"
Oldest known version of this page was edited on 2007-04-11 14:46:42 by AndreasSedlmeier []
Page view:
SQL Where (usage and tricks)
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:
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 CLIENT WHERE 1=1"
This has no impact on performance or whatever. A RDBMS optimizes this away and a RDBMS does always have an optimizer.
