Seeing Calculation View Results

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: