I'm transalting a stored procedure that we made in MSSQL.

but during conversion I have some problem....
exactly I can create a GTT into a SP?

some one can give me some idea what I have to change ?


CREATE PROCEDURE p_sample as
declare variable i_archivio varchar(8);
declare variable i_id_archivio integer;
declare variable i_vd2_codice varchar(8);
declare variable i_vd3_codice varchar(8);

declare variable FETCH_STATUS_Colors integer;
declare variable FETCH_STATUS_Seqs integer;
declare variable vd1_sequenza integer;
declare variable vdc_sequenza integer;
declare variable qta integer;
declare variable str_sequenza char(3);
declare variable str_qta char(6);
declare variable str_sequenze varchar(100);
declare variable str_qtas varchar(100);
declare variable vd1_codice varchar(8);
declare variable vd1_descrizione varchar(40);
declare variable rows_counter integer;
declare variable COLORS cursor for (select vd1_codice, descrizione from vdq
inner join vd1 on (vd1.codice=vdq.vd1_codice) where id_Archivio =
i_id_archivio and Archivio=i_archivio and vd2_codice=i_vd2_codice group by
vd1_codice, descrizione order by vd1_codice);
declare variable SEQS cursor for (select vdc_sequenza from vdd where
vd2_codice=i_vd2_codice and VD3_CODICE=i_vd3_codice order by vdc_sequenza);

begin

i_id_archivio= 25;
i_archivio= 'OVR';
i_vd2_codice= 'SHIRT';
i_vd3_codice= 'SPRT';

CREATE GLOBAL TEMPORARY TABLE VD_Temp (
nRec integer ,
COLORE varchar (10),
DES_COLORE varchar (40),
VD2_CODICE varchar (10) DEFAULT '',
SEQ_S varchar (100),
QTA_S varchar (100),
TOTAL_QTA integer);
commit;

DELETE FROM vd_temp;
commit;

open COLORS;
rows_counter = 0;
str_sequenze = '';
str_qtas = '';

for select vd1_codice, descrizione
from vdq
inner join vd1 on (vd1.codice=vdq.vd1_codice)
where id_Archivio = i_id_archivio and Archivio=i_archivio and
vd2_codice=i_vd2_codice
group by vd1_codice, descrizione
order by vd1_codice
into :vd1_codice, :vd1_descrizione
DO
BEGIN

for select vdc_sequenza, vd1_codice, vd1_descrizione from vdd
where vd2_codice=i_vd2_codice and VD3_CODICE=i_vd3_codice
order by vdc_sequenza
into :sequenza
do
begin

for select vdc_sequenza
from vdd
where vd2_codice=i_vd2_codice and VD3_CODICE=i_vd3_codice
order by vdc_sequenza
into :vdc_sequenza
do
begin
str_sequenza = cast(vdc_sequenza as char(3));
str_sequenze = rtrim(str_sequenze) + rtrim(str_sequenza) + ';';

SELECT quantita
FROM vdq
WHERE id_Archivio = i_id_archivio AND Archivio=i_archivio AND
vdc_sequenza=vdc_sequenza
AND vd2_codice=i_vd2_codice AND vd1_codice=vd1_codice
INTO :qta;

if (:qta is not null) then
begin
str_qta = rtrim(cast(qta as char(4))) + ';';
str_qtas = str_qtas + rtrim(str_qta);
end
else
begin
str_qta = rtrim(cast(0 as char(4))) + ';';
str_qtas = str_qtas + rtrim(str_qta);
end
end
end

rows_counter = rows_counter + 1;
INSERT INTO vd_temp (nRec, COLORE, DES_COLORE, VD2_CODICE, SEQ_S, QTA_S)
VALUES (rows_counter, vd1_codice, vd1_descrizione, i_vd2_codice,
str_sequenze, str_qtas);
commit;

str_qtas = '';
str_sequenze = '';

CLOSE SEQS;
END




fab10
.-----------------------------.