DELETE FROM S.I73_FNDSRC_PTYPE_T
WHERE I73_NEED_ID =
(SELECT I74_NEED_ID
FROM S.I74_NEED_SERVICE_T
WHERE I74_CAP_ID = CAP_ID);

The above code is part of a stored procedure that I'm writing. The delete statement works if the tables I73 & I74 each have only one row where I73_NEED_ID = I74_NEED_ID. However, if there is more than one row returned, I get a scalar function error and the stored procedure terminates.

Is it possible to rewrite the above code using some type of join?
Where could I find an example?

Thanks in advance.

the right high side
(SELECT I74_NEED_ID
FROM S.I74_NEED_SERVICE_T
WHERE I74_CAP_ID = CAP_ID)
is called scalar fullselect, which must result only one row(otherwise, sqlcode)

you can use '= any' predicate, such as
DELETE FROM S.I73_FNDSRC_PTYPE_T
WHERE I73_NEED_ID = ANY
(SELECT I74_NEED_ID
FROM S.I74_NEED_SERVICE_T
WHERE I74_CAP_ID = CAP_ID);

= ANY is the same as an IN-operator.

Thank you both for your replies!