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.

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

I have more than 300 records in MGP_PROT_REKL table (master to MGP_PROT_REKL_POZ
which stores items) each having 1,2 or more records in MGP_PROT_REKL_POZ linked
through SRV and PROT_ID columns - all records inserted by above procedure
without any problems, I can say even more - newer record data from mgp_role is
inserted into MGP_PROT_REKL_POZ - the problem is only with this one particular
combination of PZ_ID and SRV in MGP_ROLE table.

Where to look for the reason ...

Marcin

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <firebird-support@yahoogroups.com>
Sent: Thursday, December 09, 2004 1:14 AM
Subject: Re: [firebird-support] Multiple rows in singleton select


>
> 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
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>