Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
El_Mou
El_Mou
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
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)
psingla
psingla
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1249
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
El_Mou
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
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 ?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14971 Visits: 38985
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
psingla
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1249
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14971 Visits: 38985
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
El_Mou
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
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 ?
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19501
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
El_Mou
El_Mou
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
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 !
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search