Good day all
-0=0-=0-=0-=

We have a strange problem with stored procedures. Will appreciate any help

We have a table_a
it has the following data

select * from table_a

ID E_CODE
========== ==========

a1 a
a1 b
a1 c

Table_C is EMPTY

Then we have a stored proc (can be viewed in MetaData) : CREATE_C_ENTRY

IF we run the stored proc , the result is as follows.

/-----------------------------------------------------------------\
select * from CREATE_C_ENTRY('a1')

RC_ID RA_ID RE_CODE
=========== ========== ==========

4 a1 a
4 a1 b
4 a1 c
/-----------------------------------------------------------------\

If we run it AGAIN it is as follows
/-----------------------------------------------------------------\
select * from CREATE_C_ENTRY('a1')

RC_ID RA_ID RE_CODE
=========== ========== ==========

5 a1 a
5 a1 b
4 a1 c
/-----------------------------------------------------------------\

AND if we run it again it is as follows
/-----------------------------------------------------------------\
select * from CREATE_C_ENTRY('a1')

RC_ID RA_ID RE_CODE
=========== ========== ==========

6 a1 a
5 a1 b
4 a1 c
/-----------------------------------------------------------------\

And then stayes the same after that..

The problem is that the FOR loop in the Stored proc was suppose to do this in ONE go. Now I had to run it for the amount of entries there is
in table_A

Can somebody please give me some tips on this.

Here is a sample database

//========= SAMPLE DB META DATA ===========
/* Extract Database c:\db\toets.gdb */
CREATE DATABASE "c:\db\toets.gdb" PAGE_SIZE 1024
;


/* Table: TABLE_A, Owner: SYSDBA */
CREATE TABLE TABLE_A (ID VARCHAR(10) NOT NULL,
E_CODE VARCHAR(10) NOT NULL,
CONSTRAINT TABLE_A_PK1 PRIMARY KEY (ID, E_CODE));

/* Table: TABLE_C, Owner: SYSDBA */
CREATE TABLE TABLE_C (C_ID INTEGER NOT NULL,
A_ID VARCHAR(10) NOT NULL,
E_CODE VARCHAR(10),
DT DATE DEFAULT "NOW"
,
CONSTRAINT TABLE_C_PK1 PRIMARY KEY (C_ID));

CREATE GENERATOR GEN_EEN;

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */
CREATE PROCEDURE CREATE_C_ENTRY AS BEGIN EXIT; END ^

ALTER PROCEDURE CREATE_C_ENTRY (VA_ID VARCHAR(10))
RETURNS (RC_ID INTEGER,
RA_ID VARCHAR(10),
RE_CODE VARCHAR(10))
AS

declare variable vE_CODE varchar(10);
declare variable vC_ID integer;
BEGIN
for select e_code from table_a where id = :vA_ID into :vE_CODE do begin
select c_id from table_c where a_id = :vA_ID and e_code = :vE_CODE into :vC_ID;

if (vC_ID is null) then begin
vC_ID = gen_id(gen_een,1);
insert into table_c(c_id,a_id,e_code) values (:vC_ID,:vA_ID,:vE_CODE);
end else begin
update table_c set
c_id = :vC_ID,a_id = :vA_ID,e_code=:vE_CODE
where c_id = :vC_ID;
end
rC_ID = :vC_ID;
rE_CODE = :vE_CODE;
rA_ID = :vA_ID;
suspend;
end
END
^
SET TERM ; ^
COMMIT WORK ;
SET AUTODDL ON;
SET TERM ^ ;

/* Triggers only will work for SQL triggers */
CREATE TRIGGER TABLE_C_BI FOR TABLE_C
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.dt = "now";

END
^
COMMIT WORK ^
SET TERM ; ^

/* Grant permissions for this database */

//============ END META DATA



Thanks in Advance


Johannes Pretorius
(Programeerings Departement- Promed)

Tel : +27 11 607-3300
Faks : +27 11 622-6939
webtuiste : http://www.promed.co.za

VRYWARING

Hierdie e-pos en enige toepaslike aanhangsels is vertroulik, regsgepriviligeerd en deur landswette
beskerm. Geliewe daarop te let dat die inhoud van hierdie e-pos van 'n persoonlike aard is en
vir die uitsluitlike aandag van die geadresseerde bedoel is. Dit mag onwettig wees om enige gedeelte
van die inhoud van hierdie e-pos te gebruik, te reproduseer of vir eie gewin aan te wend. Indien
hierdie e-pos u per abuis bereik het, word u versoek om die sender onmiddelik te verwittig via e-pos
of deur (011) 607 3300 te skakel en die boodskap te vernietig. Promed vereenselwig hom
nie noodwendig met die opinies van die sender van hierdie boodskap nie en doen afstand van enige
aanspreeklikheid vir enige aksies wat geneem word op grond van inligting wat in die e-pos vervat
word, tensy dit op skrif bevestig word. Alhoewel die nodige stappe gedoen is om te verseker dat
die e-pos virusvry is, kan Promed geen aanspreeklikheid aanvaar vir die verlies of skade wat
voortspruit uit virusse in hierdie e-pos of sy aanhangsels nie.

DISCLAIMER

This e-mail and any attachments to it is confidential, may be subject to legal privilege and is
protected by law. Kindly take note that this e-mail is sent for the personal attention of the intended
recipient. It may be unlawful to use, copy, forward or disclose any of of the contents of this e-mail.
If you have received this e-mail in error, please advise us immediately via e-mail or telephone (011)
6073300 and delete this e-mail and its attachments from your computer. Promed gives no warranties
and exclude any liability for the content of this e-mail, or for the consequences of any
actions taken on the basis of the information provided in this e-mail or its attachments, unless that
information is confirmed in writing. Promed does not ratify any opinion of the sender and although we
have taken reasonable precautions to ensure that no viruses are present in this e-mail, we cannot
accept responsibility for any loss or damage arising from the viruses in this e-mail or attachments.


----------



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.1/369 - Release Date: 19/06/2006


[Non-text portions of this message have been removed]