Fórum Sintáxe para criar tabela temporária no Interbase #278614

Galera alguem pode me ajudar...

você não precisa de uma sintaxe especial, basta criar um tabela qualquer e depois dar um drop na mesma

Meu caro gostaria de saber da sintaxe... eu fiz assim
e não dá../


create temporary table xxxx as
select * from nf

Gostaria de saber qual sintaxe é usada para criar tabelas temporárias em tempo de execuçao no interbase....

Colega,

Vê se ajuda: http://www.interbase-world.com/en/articles/718.php

[quote:f63ce617e0=´Aroldo Zanela´]Colega,

Vê se ajuda: http://www.interbase-world.com/en/articles/718.php[/quote:f63ce617e0]

Grato pela ajuda,

So que o link que trata esse assunto no site ta quebrado abaixo não tem um sintaxe que cria a estrutura atraves do select dado...

Vou me virar aqui. Se alguem ja fez isso por gentileza me ajudem...

É isto que está escrito no link do zanela
When migrating from file databases or from MS SQL and ORACLE to Firebird, many developers often inquire about temporary tables. They are accustomed to using them while writing stored procedures. Though Firebird lacks temporary tables, a number of its mechanisms successfully emulate them. Below you will read about three methods of such emulation.

The first method. In most cases you can simply use the following structure: FOR SELECT INTO DO . The cursor-based loop FOR SELECT is used to execute operations (DO) on variables (INTO), which value is set equal to those of returned query fields (SELECT). And the Firebird interpreter will:

execute the query creating _a kind of_ a temporary table in RAM (or in a temporary file if the whole table is too big for RAM).

go to the first record of the Select query.

set field values of the first query record to SP variables, mentioned in ´into´ according to their order: =,=, etc.

execute operations mentioned in the block DO.

go the the second query record (if it exists) and repeat the same operations from step 3.

Full syntax of this operator is available in manuals (see for example ´LANGREF.PDF´). To some extent FOR SELECT is a temporary table and all the operations are done on it. Now I will illustrate this with a table:

// personal accounting
CREATE TABLE CASH (
ID INTEGER, // the record identifier in the table
NAME VARCHAR(30), // expenses/income
MONEY INTEGER, // the sum in dollars: with ´plus´ income (for example, wages),
//with ´minus´ expenses (for example, purchase)*
RDATE TIMESTAMP // the date of record listing);

ID NAME MONEY RDATE
1 wages 3000 10.04.2003
2 public utilities -500 05.04.2003
3 the Internet -150 22.04.2003
4 dividends 100 02.04.2003
5 wages 3000 24.04.2003
6 public utilities -400 29.04.2003

And you need to make an annual report on obtained and gone sums in such a form:

NAME INCOME EXPENSES
wages 3 000 0
public utilities 0 -500
the Internet 0 -150
dividends 100 0
wages 3 000 0
public utilities 0 -400
TOTAL 6 100 -1 050


The point is that you have to divide the column MONEY into two and count the total sum. Roughly you can:

do a query on income: SELECT NAME, MONEY FROM CASH WHERE MONEY>=0.
then do a query on expenses: SELECT NAME, MONEY FROM CASH WHERE MONEY<0.
count total income: SELECT SUM(MONEY) FROM CASH WHERE MONEY>=0.
and total expenses: SELECT SUM(MONEY) FROM CASH WHERE MONEY<0.
join the obtained results of these queries into a table in the client application.
To do this you needed altogether five queries. Temporary queries might be more effective solution. Using Firebird you will have:

CREATE PROCEDURE RESULT (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
INCOME INTEGER,
EXPENSES INTEGER)
AS
DECLARE VARIABLE SINCOME INTEGER;
DECLARE VARIABLE SEXPENSES INTEGER;
DECLARE VARIABLE TMONEY INTEGER;
BEGIN
/* Initialization of variables */
SINCOME=0;
SEXPENSES=0;
/* Selection */
FOR SELECT NAME, MONEY FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD
INTO :NAME, :TMONEY
DO BEGIN
/* Division of the MONEY field into two columns - INCOME (income) and EXPENSES (expenses) */
IF (TMONEY>=0) THEN BEGIN
INCOME=TMONEY;
EXPENSES=0;
/* Calculation of the total income sum */
SINCOME=SINCOME+TMONEY;
END
ELSE BEGIN
EXPENSES=TMONEY;
INCOME=0;
/* Calculation of the total expenses sum */
SEXPENSES=SEXPENSES+TMONEY;
END
SUSPEND;
END
/* Showing the total sum */
NAME=´TOTAL´;
INCOME=SINCOME;
EXPENSES=SEXPENSES;
SUSPEND;
END
The second method enables you to use FOR SELECT and keep previous values of table fields in SP variables. In the previous example the item ´wages´ will be shown as many times as it is mentioned in the table CASH. So if the person has got his/her wages 12 times a year, this value will be shown 12 times (and the list won´t be sorted). If you group all similar expense items and sum up corresponding columns, our table will be more readable. Then to sum up items you need to have sorting with record accumulation. For this you need to compare each item name in the current record with that of the previous one and keep intermediate results of the item calculation:

NAME INCOME EXPENSES
dividends 100 0
wages 6 000 0
the Internet 0 -150
public utilities 0 -900
TOTAL 6 100 -1 050



This will look as:

CREATE PROCEDURE RESULT2 (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
INCOME INTEGER,
EXPENSES INTEGER)
AS
DECLARE VARIABLE SINCOME INTEGER;
DECLARE VARIABLE SEXPENSES INTEGER;
DECLARE VARIABLE TMONEY INTEGER;
DECLARE VARIABLE STDOH INTEGER;
DECLARE VARIABLE STRAS INTEGER;
DECLARE VARIABLE STOLD VARCHAR(30) CHARACTER SET WIN1251;
DECLARE VARIABLE STNEW VARCHAR(30) CHARACTER SET WIN1251;
BEGIN
/* Initilization of variables */
SINCOME=0;
SEXPENSES=0;
STDOH=0;
STRAS=0;
STOLD=´´;
STNEW=´´;
/* Selection */
FOR SELECT NAME, MONEY FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD ORDER BY NAME
INTO :STNEW, :TMONEY
DO BEGIN
/* Detection of the beginning of a new item */
IF (:STOLD<>:STNEW) THEN BEGIN
/* showing of the result by the previous record */
NAME=STOLD;
INCOME=STDOH;
EXPENSES=STRAS;
IF (:NAME<>´´) THEN SUSPEND;
/* setting variables to zero to sum up by the next record */
STOLD=STNEW;
STDOH=0;
STRAS=0;
END
/* check if the summation is carried on by the same record */
IF (:STOLD=:STNEW) THEN BEGIN
/* division of income and expenses into columns and summing them up */
IF (:TMONEY>=0) THEN BEGIN
/* calculation of income in the current record */
STDOH=STDOH+TMONEY;
/* calculation of the total income */
SINCOME=SINCOME+TMONEY;
END
ELSE BEGIN
/* calculation of expenses in the current record */
STRAS=STRAS+TMONEY;
/* calculation of the total expenses */
SEXPENSES=SEXPENSES+TMONEY;
END
END
END
/* it is necessary to count the last record of the list */
NAME=STNEW;
INCOME=STDOH;
EXPENSES=STRAS;
SUSPEND;
/* showing of the total sum */
NAME=´TOTAL´;
INCOME=SINCOME;
EXPENSES=SEXPENSES;
SUSPEND;
END


There is a particular case of the first two methods - an ability to use the structure FOR SELECT INTO DO :

FOR SELECT INTO DO
FOR SELECT INTO DO

or

FOR SELECT INTO DO
BEGIN
SELECT INTO

END
Interpreting FOR SELECT as a kind of a ´temporary´ table, you can use it to work with multiple ´temporary´ tables. This may be useful if you have another family member´s table with the name CASH2 (its structure is similar to that of CASH):

CREATE TABLE CASH2 (
ID INTEGER,
NAME VARCHAR(30),
MONEY INTEGER,
RDATE TIMESTAMP);

ID NAME MONEY RDATE
1 wages 2000 01.04.2003
2 transport -200 30.04.2003



To sum up the total result of both members as:

NAME SUM
dividends 100
wages 8 000
the Internet -150
public utilities -900
transport -200
TOTAL 6 850

you can use the procedure:

CREATE PROCEDURE RESULT3 (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
SUM INTEGER)
AS
DECLARE VARIABLE S INTEGER;
DECLARE VARIABLE TMONEY INTEGER;
DECLARE VARIABLE TMONEY2 INTEGER;
BEGIN
/* Initialization */
S=0;
/* Selection */
FOR SELECT NAME, SUM(MONEY) FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD GROUP BY NAME
INTO :NAME, :TMONEY
DO BEGIN
/* Selection from the second table */
TMONEY2=0;
SELECT SUM(MONEY) FROM CASH2
WHERE RDATE BETWEEN :NGOD AND :EGOD AND NAME=:NAME
GROUP BY NAME
INTO :TMONEY2;

/* handling returned NULL records */
IF (TMONEY IS NULL) THEN TMONEY=0;
IF (TMONEY2 IS NULL) THEN TMONEY2=0;

/* summing up and showing the results */
SUM=TMONEY+TMONEY2;
S=S+SUM;
SUSPEND;
END

/* counting records that are present in the second table and lack in the first */
FOR SELECT NAME, SUM(MONEY)
FROM CASH2
WHERE RDATE BETWEEN :NGOD AND :EGOD AND
NAME NOT IN (SELECT DISTINCT NAME FROM CASH)
GROUP BY NAME
INTO :NAME, :SUM
DO BEGIN
S=S+SUM;
SUSPEND;
END
/* showing the total sum */
NAME=´TOTAL´;
SUM=S;
SUSPEND;
END
The third method is used when the task does not allow preliminary selection sorting. First you need to make a pseudo temporary table with necessary columns, fill it out in a procedure and then handle records there. To illustrate this method we will do the previous task using pseudo temporary tables.

/* we need to make a pseudo temporary table */
CREATE TABLE TEMP (
NAME VARCHAR(30), // a field for the report
INCOME INTEGER, // a field for the report
EXPENSES INTEGER, // a field for the report
TUSER VARCHAR(8), // as there may be multiple user access to the table,
it is necessary to set who a record belongs to. Otherwise
you won´t distinguish records of different users if the report will be done in several transactions

TDATE TIMESTAMP // the date of of record listing. It is necessary to control old undeleted records
)

/* The procedure of forming of the report */
CREATE PROCEDURE RESULT4 (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
INCOME INTEGER,
EXPENSES INTEGER)
AS
DECLARE VARIABLE TMONEY INTEGER;
DECLARE VARIABLE ST VARCHAR(30);
DECLARE VARIABLE SINCOME INTEGER;
DECLARE VARIABLE SEXPENSES INTEGER;
BEGIN
/* Initialization of variables */
SINCOME=0;
SEXPENSES=0;

/* Deletion of old records, which possibly remained from previous user queries */
DELETE FROM TEMP WHERE TUSER=USER;

/* Filling out the pseudo temporary table with a record list */
INSERT INTO TEMP
SELECT DISTINCT NAME, 0, 0, USER, CAST(´NOW´ AS TIMESTAMP)
FROM CASH
WHERE RDATE BETWEEN :NGOD AND :EGOD;

/* Forming of the report */
FOR SELECT NAME, MONEY FROM CASH INTO :ST, :TMONEY
DO BEGIN
IF (:TMONEY>=0) THEN
UPDATE TEMP SET INCOME=INCOME+:TMONEY
WHERE NAME=:ST AND TUSER=USER;
ELSE
UPDATE TEMP SET EXPENSES=EXPENSES+:TMONEY
WHERE NAME=:ST AND TUSER=USER;
END
/* Showing of the result */
FOR SELECT NAME, INCOME, EXPENSES
FROM TEMP
WHERE TUSER=USER
ORDER BY NAME
INTO :NAME, :INCOME, :EXPENSES
DO BEGIN
SINCOME=SINCOME+INCOME;
SEXPENSES=SEXPENSES+EXPENSES;
SUSPEND;
END
/* Showing of the total sum */
NAME=´TOTAL´;
INCOME=SINCOME;
EXPENSES=SEXPENSES;
SUSPEND;
END
The result of the procedure execution:
NAME INCOME EXPENSES
dividends 100 0
wages 6 000 0
the Internet 0 -150
public utilities 0 -900
TOTAL 6 100 -1 050


As for some reason the table TEMP may have old unnecessary records, you need to clear it regularly (using the fiel d TDATE) in such a way, for example:

/* A small hint: keep constants used in stored procedures in separate tables. This will enable you to change their values
without recompilation of the procedure */

CREATE TABLE SETVARS ( // a table of constants
NAME VARCHAR(10), // it´s better to make index on this field
SETVAR VARCHAR(50),
REMARK VARCHAR(50)
)

NAME SETVAR REMARK
OLDTEMP 10 Deletion of old unnecessary records (older than 10 days)

/* Clearing procedure */
CREATE PROCEDURE CLEARTEMP
AS
DECLARE VARIABLE T INTEGER;
BEGIN
/* determining the variable ´T´ from the table of constants */
FOR SELECT CAST(SETVAR AS INTEGER)
FROM SETVARS
WHERE NAME=´OLDTEMP´
INTO :T DO
/* deleting all old records */
DELETE FROM TEMP
WHERE TDATE<(CAST(´NOW´ AS TIMESTAMP)-:T);
END
The third method is the slowest one because you go through the table CASH twice, insert and update records in a pseudo temporary table and only then show the result. And from time to time it is also necessary to clear the table Temp. The first and second methods are more quick (they will be executed almost at the speed of record selection). I know from experience that indexing the table CASH by the sorting field NAME helps to get first query results in a few seconds even from tables with about 10 million records. And applying the third method you will get the same result time from tables with about 10 thousand records. See below test results of RESULT2 and RESULT4 procedure execution (IBExpert´s data)



Parameter RESULT2 RESULT4
Query Time
Prepare 0,00 ms 0,00 ms
Execute 15,00 ms 16,00 ms
Avg fetch time 3,00 ms 3,20 ms
Operations
Fetches 15 570


So first it is recommended to use the first (with FOR SELECT) and the second (FOR SELECT with keeping of previous values) methods. As a last resort, you can use the third method of pseudo temporary tables.

Meu caro esse texto eu vi no texto existe um link que é esse q indica o assunto no qual eu quero:

Lembro que na sintaxe do MySQL que uso, eu faço

Create temporary table [nome_da_tabela]
select * from [nome_da_tabela]

Logo após dou um Drop table [nome_da_tabela]


No texto o create table é do mesmo modo, de que eu estivesse
criando uma tabela normal. Ele esta dizendo informando a criaçao de uma tabela com o nome cash, informando sua estrutura.


[b:476c8976fc]A minha duvida é qual sintaxe uso para criar uma tabela temporaria
em cima de um select onde toda estrutura da tabela que estou selecionando estará na tabela criada na memória.[/b:476c8976fc]

Agradeço pela ajuda de vocês irei me virar aki...

Colega,

O artigo pode não ser atual, em face de isto poder estar contemplado na versão 1.5. Mas no artigo deixa claro que não existe tabelas temporárias (naquela época) e dá três exemplos para emulação.