Implementing Scalar Queries with Magic xpa (Magic xpa 3.x)
The aim of this technical note is to show an easy and simple way to implement scalar queries.
A SELECT clause in a SELECT statement can contain columns, constants and functions. One type of function that can be used is a scalar query, which is a single-row subquery.
For example:
Select
Customer_Code,
Customer_Name,
(
Select City_Name
From Cities
Where Cities.City_Code=Customers.City_Code
) City_Name,
(
Select sum(Total*(100+Vat)/100)
From Order_Titles
Where Order_Titles.Customer_Code=Customers.Customer_Code
) Purchases
From Customers
Order by 3, 4
The result of this query is a list of customers, their respective city and the total sum they paid for their purchases, sorted by the city and the total.
Normally, you would create two tasks for this type of query:
-
A parent task that would use the Customers table as the main data source and link to the Cities table.
-
A subtask that would calculate the total of the respective customer’s orders and save it in a virtual in the upper task.
To be able to sort the result, Magic xpa would require this information to be loaded in a temporary table.
Alternatively, a direct SQL command could be used (for Online tasks, a temporary table would still be created).
Both methods have some disadvantages in terms of cost effectiveness and ease of implementation.
To achieve the same result, we can use a simple trick:
-
Make sure that the Change Tables in Studio database property (Toolkit for older versions) is off (unchecked).
-
In the Data repository (Table or File repository for older versions), replicate the Main Source (Main table in older versions) that you want to process.
-
At the bottom of the table’s Column repository, add a new column, City_Name and change its DB Column name to the corresponding scalar query.
Please note that this is not a real column; it does not exist in the SQL database.
-
Use the same method to define a column that will contain the customer's total.
-
Now, simply create an index that will allow an easy sort based on these new columns and generate a simple browse program utilizing the new index.
-
Since these columns do not exist in the database, trying to update or create records that contain them will result in an error message.
-
This table cannot be used in conjunction with Inner or Outer Joins, since the query generated would be illegal.
-
Since the DB Column name length is limited to 300 characters, use a function if the query length exceeds this limitation.
-
It is your responsibility to make sure that the scalar query will return a single value; otherwise, an error will occur.
-
It is your responsibility to account for differences in the SQL syntax of the various databases.
General Remark:
It is recommended to replicate the Main Source and use the original data source entry for update/create programs.
