DB SQL Where (Magic xpa 3.x)
The DB SQL Where feature is available for tasks with an SQL Main source only.
The purpose of the DB SQL feature is to enable the programmer to use the SQL- specific Where clauses (in addition to the Where clauses generated automatically by Magic xpa) without the need to use the Direct SQL tasks, and to view the Full Where clause that is generated by Magic xpa.
|
Note:
|
-
When using a DB SQL Where clause in a deferred transaction task, any update done in another task on the queried data source will not be seen. DB SQL Where clauses are supported in deferred transaction tasks. Since version: 1.8
-
In Space databases, the data sources and column names are case sensitive. When creating data sources manually or when using a DB SQL Where clause, make sure to you use the correct case.
-
In Space databases, for the DB SQL Where and Magic SQL clause, a field name on the right side of an expression is not supported. The following, for example, is not supported: a = a1.
|
This is an interface for writing Range based on SQL syntax using Magic xpa columns. This range is added to the Magic xpa Where clause that is defined in the Range dialog box.
You can zoom to the column list from the DB SQL field. The variable list is a list of all the variables available to use in the DB SQL property:
-
Columns from the main sources and joined tables – will be replaced with their DB column name.
-
Variables from parent tasks – will be replaced with their values.
-
Virtual and other columns from the current task – will be replaced with their values.
The DB SQL refers to a free-text form in which the DB SQL Where clause is written. Two types of strings can be written in the DB SQL area. They are as follows:
-
A column number (A, B, C, etc.) prefix with a ‘:’ sign. If the column is from either the main or joined tables it will be replaced by its DB column name. Otherwise, it will be replaced by its value according to its attribute. For Alpha columns, Magic xpa will suppress any trailing blanks, and add quotes to it.
-
A column number (A, B, C, etc.) prefix by ‘@:’ sign and is an Alpha column not from the main or joined tables. It will be replaced with its value without adding quotes to it.
When Magic xpa replaces a column with its contents, Magic xpa checks the column’s attribute and storage, and if necessary adds quotes - as in the case of Alpha strings. In order to prevent Magic xpa from adding quotes to the alpha columns, which enables any type of syntax to be written, the @ character should be added as a prefix to the column.
For example, assuming
A is real column with DB name Employee. jobname, and B is a virtual Alpha column with description Vjobname, and its value in Runtime is “AB” then writing the DB SQL range
:A like ‘B% ’ will be displayed as
Employee. jobname like ‘B% ’
(the table name will be added only when link join is involved)
and will translate in Runtime to
jobname like ‘B% ’
Writing the DB SQL range
:A like :B will be displayed as
Employee. jobname like [ “Vjobname”]
(the table name will be added only when link join is involved)
and will translate in Runtime to
jobname like ‘AB’
Assuming that C is a virtual column, and its Description (Name) is Voperation and its value in Runtime is “like” then writing the DB SQL range
:A @: C :B will be replaced with
jobname [Voperation] [“ Vjobname”]
and will translate in Runtime to
jobname like ‘AB’
Magic xpa SQL Where and DB SQL Where Usage Considerations
