Subquery returned more than 1 value. This is not permitted when the subquery follows

Question

Subquery returned more than 1 value. This is not permitted when the subquery follows

El_Mou

SSChasing Mays

Points: 621
More actions
June 26, 2013 at 5:12 am

#279611

hello all
i have a troubles with Stored Procedure ( i need it for my SSRS report ) , and i dont understand why they wont execute :
this is my DDL SP :
CREATE PROCEDURE [dbo].[SP_calcul]
AS
select distinct
FACT_INTERVENTION.ID_INTER,
FACT_INTERVENTION.LIB_INTER,
DIM_INTER_MATERIEL.DUREE_UTILISATION_MATERIEL
,DIM_INTER_MATERIEL.QUANTITE_MATERIEL
,DIM_INTER_MATERIEL.UNITE_COUT_MATERIEL
,FACT_INTERVENTION.SUPERFICIE_INTER
,FACT_INTERVENTION.SUPERFICIE_INTER_CULTURE
from
FACT_INTERVENTION FULL OUTER JOIN DIM_INTER_MATERIEL
ON FACT_INTERVENTION.ID_INTER = DIM_INTER_MATERIEL.ID_INTER
declare @DU_QTE real
if (select UNITE_COUT_MATERIEL from DIM_INTER_MATERIEL) = 'Unité'
begin
set @DU_QTE = (select (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * QUANTITE_MATERIEL from FACT_INTERVENTION inter,DIM_INTER_MATERIEL mat
where inter.ID_INTER=mat.ID_INTER )
print cast(@DU_QTE as varchar (50));
end
AND THIS IS THE MESSAGE ERROR :
(386613 row(s) affected)
Msg 512, Level 16, State 1, Procedure SP_calcul, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
(1 row(s) affected)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

psingla Hall of Fame Points: 3840 More actions · Answer 1

select UNITE_COUT_MATERIEL from DIM_INTER_MATERIEL

select (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * QUANTITE_MATERIEL from FACT_INTERVENTION inter,DIM_INTER_MATERIEL mat

where inter.ID_INTER=mat.ID_INTER

both the queries should return only single value..Use top 1

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

El_Mou SSChasing Mays Points: 621 More actions · Answer 2

psingla (6/26/2013)
select UNITE_COUT_MATERIEL from DIM_INTER_MATERIEL
select (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * QUANTITE_MATERIEL from FACT_INTERVENTION inter,DIM_INTER_MATERIEL mat
where inter.ID_INTER=mat.ID_INTER
both the queries should return only single value..Use top 1

CAN YOU EXPLAIN MORE PLEASE ?

Lowell SSC Guru Points: 323518 More actions · Answer 3

you have two queries that could potentially be comparing more than one row to a single value;

one is a simple test for a value:

if (select UNITE_COUT_MATERIEL from DIM_INTER_MATERIEL) = 'Unité'

begin

end

and the other is assigning a calculation to a variable.

set @DU_QTE = (select (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * QUANTITE_MATERIEL from FACT_INTERVENTION inter,DIM_INTER_MATERIEL mat

where inter.ID_INTER=mat.ID_INTER )

So the issue is if multiple rows exist, what calculation do you really want? would the sum() work where there are multiple rows? don't you need to filter the calculation as well?

I would consider changing it to something like this:

--is there ANY data which matches this criteria?

if EXISTS (select 1 from DIM_INTER_MATERIEL WHERE UNITE_COUT_MATERIEL = 'Unité')

begin

select @DU_QTE = (SUM(SUPERFICIE_INTER_CULTURE) / SUM(SUPERFICIE_INTER)) * SUM(QUANTITE_MATERIEL)

from FACT_INTERVENTION inter

INNER JOIN DIM_INTER_MATERIEL mat

ON inter.ID_INTER=mat.ID_INTER

WHERE UNITE_COUT_MATERIEL = 'Unité'

print cast(@DU_QTE as varchar (50));

end

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

psingla Hall of Fame Points: 3840 More actions · Answer 4

use

if (select TOp 1 UNITE_COUT_MATERIEL from DIM_INTER_MATERIEL)= 'Unité'

or

if exists (select 1 from DIM_INTER_MATERIEL where UNITE_COUT_MATERIEL = 'Unité')

both the above query will work in your case.

sub query should return single value if you are using =operator to compare/assign the result of the query.

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Lowell SSC Guru Points: 323518 More actions · Answer 5

psingla (6/26/2013)
use
if (select TOp 1 UNITE_COUT_MATERIEL from DIM_INTER_MATERIEL)= 'Unité'
or
if exists (select 1 from DIM_INTER_MATERIEL where UNITE_COUT_MATERIEL = 'Unité')
both the above query will work in your case.
sub query should return single value if you are using =operator to compare/assign the result of the query.

you'd want to avoid the top 1 example; without a ORDER BY statement, and if that table has multiple rows, there's no way to know if the randomly selected top row would actually be what you were looking for, unless you KNOW that table only ever has a single row in it.

The EXISTS is my preference, just like you posted here.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

El_Mou SSChasing Mays Points: 621 More actions · Answer 6

its not the best practice to create a calculated field by calling this stored procedure

so this is my context : i have a rapport who work perfectly but i want to add a calculated field

formula of my field should be like this

if UNITE_COUT_MATERIEL = 'Unité' then (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * QUANTITE_MATERIEL

if UNITE_COUT_MATERIEL = 'h' then (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * DUREE_UTILISATION_MATERIEL

HOW CAN I PUT HIS IN THE FONCTION EXPRESSION DESIGNER OF MY DATASET ?

Phil Parkin SSC Guru Points: 246993 More actions · Answer 7

El_Mou (6/26/2013)
its not the best practice to create a calculated field by calling this stored procedure
so this is my context : i have a rapport who work perfectly but i want to add a calculated field
formula of my field should be like this
if UNITE_COUT_MATERIEL = 'Unité' then (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * QUANTITE_MATERIEL
if UNITE_COUT_MATERIEL = 'h' then (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * DUREE_UTILISATION_MATERIEL
HOW CAN I PUT HIS IN THE FONCTION EXPRESSION DESIGNER OF MY DATASET ?

Something like this?

select NewCol = (case UNITE_COUT_MATERIEL

when 'Unité' then SUPERFICIE_INTER_CULTURE * QUANTITE_MATERIEL / SUPERFICIE_INTER

when 'h' then SUPERFICIE_INTER_CULTURE * DUREE_UTILISATION_MATERIEL / SUPERFICIE_INTER

else 0

end)

Notes

1) I changed the order of the arithmetic operators, as it is usually better to multiply before dividing (reduced likelihood of rounding errors).

2) I added an 'else return zero' condition, to catch other alternatives.

--edit: fixed typo

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

El_Mou SSChasing Mays Points: 621 More actions · Answer 8

THANKS FOR YOUR HELP

BUT NOW I WANT TO CALCULATE THIS FIELD IN MY SSRS REPORT

HOW CAN I PUT IT THERE ? I FOUND JUST 3 FONCTIONS : SWITCH , IF and CHOOSE

I TRIED SWITCH & IFF BUT IT DOESNT WORK THERE IS SOMETHING WRONG !