Welcome! Log In Create A New Profile

Advanced

Temporary tables in HyperFile

Posted by Sebastian Arnold 
Sebastian Arnold
Temporary tables in HyperFile
August 05, 2011 01:44PM
From a different DBMS:

"Temporary tables are otherwise identical to regular tables except for two unique properties that make them ideal for storing temporary or intermediate data for complex transactions. First, temporary tables are visible only on the connection where the temporary tables are created. This eliminates the possibility of name conflict when creating a temporary table. There could be multiple temporary tables named "Temp1" residing on different connections. Secondly, temporary tables are automatically deleted when the connection that creates them is closed. In other words, the scope of the temporary table is the current connection. This removes responsibility of maintaining the temporary table from the application."

Could be created like this:

// This example creates two temporary tables for intermediate results
// Step 1. Create a temporary table named DeptCount and at the same time 
// populate it with summary data from an existing table in the 
// database

SELECT deptnum, count(*) as NumEmployees
INTO #DeptCount
FROM employees
GROUP BY deptnum

// Step 2. Create another temporary table named LocCount which list the 

// number of employees in each location for each department.

SELECT deptnum, location, count(*) as cnt
INTO #LocCount
FROM employees
GROUP BY deptnum, location

// Finally using the 2 temporary tables to list the percent of employee
// on each location for each department

SELECT a.deptnum, a.location, ( a.cnt * 100 ) / b.NumEmployees As PercentAtLocation
FROM #LocCount a, #DeptCount b
WHERE a.deptnum = b.deptnum
Al
Re: Temporary tables in HyperFile
August 07, 2011 07:11AM
Hello Sebastian

Check out the fOpenTempFile (Function)

Regards
Al
Sebastian Arnold
Re: Temporary tables in HyperFile
November 14, 2011 08:41PM
Well, yes, kind of. I guess you can't have a sql statement on a temporary file like this.

Thanks anyway.

Sebastian
Author:

Your Email:


Subject:


Spam prevention:
Please, enter the code that you see below in the input field. This is for blocking bots that try to post this form automatically. If the code is hard to read, then just try to guess it right. If you enter the wrong code, a new image is created and you get another chance to enter it right.
Message: