Prev: UPDATE through IN(..) behaviour
Next: Is there any function to convert the first letter to upper case?
From: DavidC on 12 Jan 2010 17:59
I am trying to set a value in a variable and am getting the error "Subquery
returned more than 1 value. This is not permitted when the subquery follows
=, !=, <, <= , >, >= or when the subquery is used as an expression."

The set line it fails on is below. How can I work around this? Thanks.

SET @FedWHAllow = (SELECT FedWHAllow FROM PayFreq WHERE PayFrequency =
(SELECT PayFrequency FROM BranchPayrollGroups WHERE PayrollGroup =
@PayrollGroup));

--
David
From: Plamen Ratchev on 12 Jan 2010 18:46
You can change the WHERE clause predicate in the subquery to guarantee only a single row is returned. Alternatively you
can use the MIN/MAX aggregate functions to select only the MIN/MAX value.


SET @FedWHAllow = (SELECT MAX(FedWHAllow)
FROM PayFreq AS P
JOIN BranchPayrollGroups AS B
ON P.PayFrequency = B.PayFrequency
WHERE B.PayrollGroup = @PayrollGroup);

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 12 Jan 2010 23:57
>> The set line it fails on is below.  How can I work around this? <<

The short answer is to kludge this so the subquery returns one of the
many values it found; use a MIN() or MAX().

But the answer is that your DDL is probably screwed up. Pay frequency
is an attribute of a payroll group and a table called PayFreq,
guessing at what you posted

SET @out_fed_withhold_allow
= (SELECT fed_withhold_allow
FROM PayFreq
WHERE pay_frequency
= (SELECT pay_frequency
FROM BranchPayrollGroups
WHERE payroll_group_id = @in_payroll_group_id));

I would think that the federal withholding allowance would be an
attribute in the BranchPayrollGroups table, like pay_frequency is.
From: DavidC on 13 Jan 2010 08:59
The Federal W/W allowance is the same for all groups. It is the *state*
allowance that is separated by state. Thank you for you reply. It really
helped.
--
David


"--CELKO--" wrote:

> >> The set line it fails on is below. How can I work around this? <<
>
> The short answer is to kludge this so the subquery returns one of the
> many values it found; use a MIN() or MAX().
>
> But the answer is that your DDL is probably screwed up. Pay frequency
> is an attribute of a payroll group and a table called PayFreq,
> guessing at what you posted
>
> SET @out_fed_withhold_allow
> = (SELECT fed_withhold_allow
> FROM PayFreq
> WHERE pay_frequency
> = (SELECT pay_frequency
> FROM BranchPayrollGroups
> WHERE payroll_group_id = @in_payroll_group_id));
>
> I would think that the federal withholding allowance would be an
> attribute in the BranchPayrollGroups table, like pay_frequency is.
> .
>
 | 
Pages: 1
Prev: UPDATE through IN(..) behaviour
Next: Is there any function to convert the first letter to upper case?