We will now generate a SQL
select statement for our AV_ORDERS
calculation view. It will be editted to query all rows between 6 months and 18 months ago. Since ORDERS_CS
contains all data from the current year and ORDERS_DT
contains 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 ORDERS_CS
into ORDERS_DT
.
Click execute, to run the statement.
CALL "TPCH"."Migrate_Aged_Orders"();
Now, right click on the calculation view AV_ORDERS
and click Generate Select SQL.
The result:
We will add a WHERE
statement, 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_CS
and ORDERS_DT
. Add the following between the FROM
and GROUP BY
lines:
WHERE "O_ORDERDATE"
BETWEEN ADD_YEARS(CURRENT_DATE, -1.5)
AND ADD_YEARS(CURRENT_DATE, -0.5)
Click execute.
The results should look like the following image: