At 11:53 PM 8/12/2004 +0100, you wrote:

>Hello
>
>I have a statement in a stored procedure
>
> 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;
>
>and when executed returns 'multiple rows in singleton select' even if
>executed
>separately. The select part returns only ONE row in this particular case
>(accordingly to 'where' parameters values).
>What can be the reason - this statement worked hundred of times but returns
>error in this case.
>
>Structure of table MGP_PROT_REKL_POZ is
> SRV CHAR(3) NOT NULL,
> ID INTEGER NOT NULL,
> PR_ID INTEGER,
> ROLA_ID INTEGER,
> USZK_ILE INTEGER,
> USZK_OPIS VARCHAR(100)
>
>the result of the select part is:
>
>"PIG";843;842;226417;0;""

It's the select on MGP_ROLE that is causing the error. Your select
specification must be able *logically* to return one and only one set.
Looking at your specification, it is the WHERE clause that determines which
rows will be selected from MGP_ROLE:

SELECT
SRV,
GEN_ID(MGP_GEN_PROT_REKL,1),
/* :PR_ID, */ should be PR_ID,
ID,
CAST(0 AS INTEGER), /* cast is unnecessary */
CAST('' AS VARCHAR(100)) /* ditto */
FROM MGP_ROLE
WHERE
SRV = :SRV
AND PZ_ID = :PZ_ID
AND USZK = 1;

These selection (WHERE) criteria are potentially returning more than one ID
for that combination.

Change this to
SELECT
SRV,
GEN_ID(MGP_GEN_PROT_REKL,1),
:PR_ID,
min(ID),
0,
''
FROM MGP_ROLE
WHERE
SRV = :SRV
AND PZ_ID = :PZ_ID
AND USZK = 1
GROUP BY 1,2,3,5,6;

However, it does beg the question of whether you are creating a data
integrity problem here. There is nothing here that is able to determine
the correct ID in cases where there are multiple IDs representing the same
combination of SRV + PZ_ID + USZK.

./heLen