I need to list all deliveries for where at least one delivery for a trackid
falls within parameter dates.

My sp throws "multiple rows in singleton select" whenever more than one
order is returned

Set Term ^;
Create procedure List_Deliveries (DepotId VarChar(25), PrnId VarChar(15),
aFrom Date, aTo Date)
returns (TrackId VarChar(20), ConId VarChar(15), Upliftment Char(1), InvNo
VarChar(20)
, InvValue Numeric(15,2), LoadCartons Integer, DelDate Date,
DelCartons Integer
, DelCode VarChar(15))
as
Declare Variable bTrack VarChar(25);
begin
/* Find distinct trackid that meet parameters (any delivery within
specified dates, depotid and prnid) */
for select Distinct T.TrackId from TrpTrn T
where T.DepotId=:DepotId and T.PrnId=:PrnId and (DelDate between
:aFrom and :aTo)
into :bTrack
do
/* return all transactions for above trackid (and depotid and
PrnId) */
/* must return all transactions even if outside date as long as at
least one transction is within date parameters */
begin
select S.TrackId,S.ConId, S.Upliftment, S.InvNo, S.InvValue,
S.LoadCartons, S.DelDate, S.DelCartons, S.DelCode
from TrpTrn S
where S.DepotId=:DepotId and S.PrnId=:PrnId and S.TrackId=:bTrack
into :TrackId,:ConId, :Upliftment, :InvNo, :InvValue, :LoadCartons,
:DelDate, :DelCartons, :DelCode;
suspend;
end
end^


Table TrpTrn

DepotId PrnId TrackId DelDate InvValue LoadCartons DelCartons DelCode .....
23 21 01 02.03.2005 1500 5 5 1
23 21 02 15.02.2005 1500 5 5 1
23 21 02 16.03.2005 1500 5 5 1
23 21 02 17.04.2005 1500 5 5 1
23 21 03 01.05.2005 1500 5 5 1
23 21 04 01.06.2005 1500 5 5 1

select * from List_deliveries('23',21','01.03.2005','31.05.2005') should
return
DepotId PrnId TrackId DelDate InvValue LoadCartons DelCartons DelCode .....
23 21 01 02.03.2005 1500 5 5 1
23 21 02 15.02.2005 1500 5 5 1
23 21 02 16.03.2005 1500 5 5 1
23 21 02 17.04.2005 1500 5 5 1
23 21 03 01.05.2005 1500 5 5 1

Thanks Cao