Magic xpa SQL Where Range Expression (Magic xpa 4.x)
The Magic xpa SQL Where range is similar to the DB SQL Where range with one exception – it is defined using a subset of Magic xpa expressions (unlike the DB SQL Where range, which uses free format text).
The syntax is purely Magic xpa syntax, but it will be translated to create the underlying SQL Where command at runtime.
The Magic xpa SQL Where range can only include expressions that the engine can translate to SQL.
For example, assuming:
A is a real column with a DB name of Employee.BirthDate, then writing the Magic xpa SQL range expression A = DATE() will be displayed in the Full SQL Where area as:
Employee. BirthDate = DATE()
(the table name will be added only when link join is involved)
Note that the Magic xpa Date function is used, not a DB-specific function.
This will be translated in Runtime with Oracle to:
TO_Date(Employee.BirthDate, ‘DD-MON-YY’) = TO_DATE(SYSDATE, ‘DD-MON-YY’)
and will translate in Runtime with MSSQL to:
CAST(CONVERT(CHAR, Employee.BirthDate,112) AS DATETIME) = CAST(CONVERT(CHAR, GETDATE(),112) AS DATETIME)
The same task was executed without any change in the syntax of the expression against two different databases. Magic xpa translates the Magic xpa SQL Where expression to the appropriate syntax per each database.
When using a Magic xpa SQL expression, the following restriction applies:
Magic xpa assumes that the columns used in the expression are mapped to database columns using the default mapping , so that any value used in the expression is sent using the expected data type.
For example, assuming that you use the following expression:
B>='03:03:03'TIME
Magic xpa assumes that B, which is a Time field in Magic xpa , is mapped to Char(6) in the database; thus it sends '03:03:03'TIME as '030303' as part of the Where clause.
Magic xpa SQL Where and DB SQL Where Usage Considerations
Magic xpa SQL Functions
How Do I Set Data Source Mappings to Support Working with Multiple DBMSs?
