Seeing Calculation View Results
- How to generate a
SQLSelect Statement for a Calculation View
- How to Visualize Queries
- Tutorials: “Adding Our Union Calculation View into the Converted Data Model” and “Migrate Records in Related Tables Using Stored Procedure”
- Step 1
We will now generate a
SQLselect statement for our
AV_ORDERScalculation view. It will be editted to query all rows between 6 months and 18 months ago. Since
ORDERS_CScontains all data from the current year and
ORDERS_DTcontains all data older than a year, the result set will span both the in-memory table instance and the extended table instance.
We will then visualize the plan, and show again that data is being queried from both in-memory and extended storage.
Before we begin, open a SQL console, and run our
Migrate_Aged_Orders()function created from our previous tutorial, Migrate Records in Related Tables Using Stored Procedure. This will ensure that all data older than one year is moved from
Click execute, to run the statement.sqlCopy
Now, right click on the calculation view
AV_ORDERSand click Generate Select SQL.
We will add a
WHEREstatement, so the query only returns results between 6 to 18 months ago. This is done to ensure that it is collecting data from both
ORDERS_DT. Add the following between the
WHERE "O_ORDERDATE" BETWEEN ADD_YEARS(CURRENT_DATE, -1.5) AND ADD_YEARS(CURRENT_DATE, -0.5)
The results should look like the following image:
- Step 2
We can also visualize the query, by going back to the
SQLtab, right clicking, and selecting Visualize Plan > Execute.
After it executes, navigate to the Executed Plan tab.
On the bottom panel, navigate to the Operator List tab, and search for the operator name ES.
Double click one of the results, and it will zoom into the selected operator. As you can see by the Remote Row Scan box, the query accessed the extended table.
Which operator name do you search for?