At 01:49 AM 9/12/2004 +0100, you wrote:

>Helen
>
>I had a combination of SRV and PZ_ID parameters that returned more than
>one row
>(different ID values) and it worked properly that's why I have used INSERT
>INTO
>A_TABLE SELECT ... method.
>
>The problem where parameters are PIG for SRV and 11484 for PZ_ID and there is
>only one record that have USZK set to 1.
>SRV and ID columns from MGP_ROLE table are the primary key of this table so
>there are no duplicates.

There is an implicit redundancy in this, for the relationship with
MGP_PROT_REKL_POZ. If you need *both* SRV and ID in the primary key of
MGP_ROLE, then one assumes that the ID is a unique value coming from some
other table.

>The procedure is :
>
>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;
> 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);
> 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;
> END
> suspend;
>end
>
>that should clarify why I have used :PR_ID instead of PR_ID

Actually, sorry, I tagged that initially and then realised that you did in
fact need the constant there...sorry I forgot to erase that.

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.

I see the problem as deeper than looking for a workaround to avoid the
exception. If you have some redundancy in that structure, then you have
two choices, as far as I can see, viz.
either
1) write an executable SP that is called by the main procedure in lieu of
the INSERT INTO....SELECT FROM structure, making certain that the
RETURNING_VALUES from the called procedure returns only the exactly correct
set
or
2) add sufficient criteria to your WHERE clause to ensure that the SELECT
can target one and only one row in the subquery

As long as you are getting multiple IDs that meet the WHERE criteria, you
have a data integrity problem: either you need to add structure to allow
the 1:many relationship, or you need to remove the redundancy that is
making it possible to violate a 1:1 relationship.

./heLen