At 08:58 AM 9/12/2004 +0100, you wrote:

>Hello
>
> > I don't know how, in the past, you got away with the multiple rows problem
> > but I can only suppose that you are encountering this problem only where
> > you really do get duplicate ids.
>
>Helen, there is duplicate IDs since they are taken from primary key of
>MGP_ROLE
>table
>
>Sorry typo here - should be there is No duplicate IDs

Look, let's simply this.

Here is your procedure, with some comments by me because we seem to be
going round and round in circles.

CREATE PROCEDURE MGP_PZ2PROT_REKL (
SRV VARCHAR (3),
PZ_ID INTEGER)
RETURNS (
PR_ID INTEGER)
AS

DECLARE VARIABLE DATA_WEJ DATE;
DECLARE VARIABLE MAG_W VARCHAR(5);
begin

IF (EXISTS(SELECT R.ID FROM MGP_ROLE R WHERE R.SRV = :SRV AND R.PZ_ID =
:PZ_ID
AND R.USZK = 1))
THEN BEGIN
SELECT DATA_WEJ, MAG_W FROM MGP_PZ
WHERE SRV = :SRV AND PZ_ID = :PZ_ID
INTO :DATA_WEJ, :MAG_W;
The above will cause a "Multiple rows..." error if there is more than one
row in MGP_PZ that has the same combination of SRV and PZ_ID

PR_ID = GEN_ID(MGP_GEN_PROT_REKL,1);
INSERT INTO MGP_PROT_REKL(PR_ID,SRV,PZ_ID,DATA_PR,MAG_W)
VALUES (:PR_ID, :SRV, :PZ_ID, :DATA_WEJ, :MAG_W);

No problem there.

INSERT INTO MGP_PROT_REKL_POZ
SELECT SRV, GEN_ID(MGP_GEN_PROT_REKL,1),:PR_ID, ID,CAST(0 AS
INTEGER),CAST('' AS VARCHAR(100))
FROM MGP_ROLE WHERE SRV = :SRV AND PZ_ID = :PZ_ID AND USZK = 1;

The above will cause a "Multiple rows..." error if there is more than one
row in MGP_ROLE that has the same combination of SRV and PZ_ID

END
suspend;
This is an executable SP and suspend should not be there.
end

So the procedure has two potential places where multiple rows would cause
problems. Only you know your metadata and their relationships, but your
problem lies in one or both of those statements.

./heLen