Advantage Developer Zone

 
 
 

Using Temporary Table Tip 1: Passing Parameters To Views

Friday, July 14, 2006

The ability to define SQL views is a powerful feature available using Advantage Data Dictionary. Views are logical tables that are created as needed using the data from one or more physical tables. A view can be treated just as a regular table. It can be used in SQL queries just like a regular table. Permissions can be granted to views to limit access to the views. However, just like using a physical table, it is not possible give parameters to a view to tailor the data presented in the view.

With the Temporary Table feature available in the 7.1 release of the Advantage products, it is now possible to dynamically alter the data returned from the views based on specific runtime condition of each application. Each connection to a Advantage database has its own name space for temporary tables so it is possible to have same named temporary table available in different connections and for different user. This makes temporary tables an ideal place to stored values that are local to each connection and those local values can then be used by the views just like using parameters. The following example illustrates how to use a view to provide a count of number of employees in one or more departments depending on some input. The example is contrived to illustrate the idea of passing parameters to views. There are many other easier way to achieve the desired result.

1) Define a view using a temporary table as part of the input.

CREATE VIEW emp_count AS
SELECT count(*) cnt
FROM employees e
WHERE e.dept IN ( SELECT dept FROM #emp_count_params );

Note: A dummy #emp_count_params table should be created first to allow the SQL engine to verify the correctness of the view definition.

2) Before using the view, create the temporary parameter table and add the desired parameter values

CREATE TABLE #emp_count_params ( dept char(30) );
INSERT INTO #emp_count_params VALUES ( 'R&D' );

3) Use the view

SELECT * FROM emp_count

will return the number of employees in the 'R&D' department.

cnt
-----
7

4) Delete the temporary table to release the resources

DROP TABLE #emp_count_params

Give the view definition above, it is also possible to get the count from multiple departments by inserting the names of the required departments into the temporary parameter table:

CREATE TABLE #emp_count_params ( dept char(30) );
INSERT INTO #emp_count_params VALUES ( 'R&D' );
INSERT INTO #emp_count_params VALUES ( 'Technical Support' );
SELECT * FROM emp_count

will return the number of employees in the 'R&D' and the 'Technical Support' departments.

cnt
-----
10

There is no limit to how temporary tables may be used in views or regular SQL queries. The example above showed how they may be used to simplify application programming. Used judiciously, they can also improve application performance as well - a subject for another Tech Tip.