Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Subquery returned more than 1 value. This is not permitted when the subquery follows Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 5:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
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)
Post #1467569
Posted Wednesday, June 26, 2013 5:28 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 11:51 PM
Points: 608, Visits: 1,035
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 | MCSA SQL Server 2012

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Post #1467577
Posted Wednesday, June 26, 2013 5:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
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 ?
Post #1467586
Posted Wednesday, June 26, 2013 5:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467592
Posted Wednesday, June 26, 2013 5:48 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 11:51 PM
Points: 608, Visits: 1,035
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 | MCSA SQL Server 2012

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Post #1467594
Posted Wednesday, June 26, 2013 5:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467599
Posted Wednesday, June 26, 2013 7:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
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 ?
Post #1467645
Posted Wednesday, June 26, 2013 11:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:06 PM
Points: 5,047, Visits: 11,797
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1467961
Posted Thursday, June 27, 2013 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
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 !
Post #1468210
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse