Click here to Skip to main content
15,890,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
ALTER procedure [dbo].[track_order1]
@grnid int,
@insid int,
@piid int
as
begin
Select distinct pii.inst_id, pd.PIid, pd.title,pd.publisher,pd.format ,pd.subscription_period_from as Sub_from,
pd.subscription_period_to as sub_to,pd.Qty_in_year AS Frequency
,(Select distinct(volumn) from  Stock_Dispatch  where inst_id=@insid and PIid=@piid and title= pd.title) As Volumn,Coalesce((Select Sum(cast(Dispatch_Qty  as int)) 
 from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
title= pd.title and order_Status=2) ,0)as Rec_qtys,
pd.Qty_in_year -Coalesce((Select Sum(cast(Dispatch_Qty  as int)) 
 from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
title= pd.title and order_Status=2) ,0) as rem,
Coalesce((Select Sum(cast(Dispatch_Qty  as int)) from 
dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=1),0) as New_dispatch,
 case pd.Qty_in_year -Coalesce((Select Sum(cast(Dispatch_Qty  as int)) 
 from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
title= pd.title and order_Status=2) ,0) when 0 then 'Complete'
else 'Not Completed' end as Order_stat
from  dbo.Performa_details pd, Client_Goods_details_master po
,Stock_Dispatch sd right outer join Performa_details pdio on pdio.PIid=sd.PIid , 
Performa_invoice pii where po.PIid=pd.PIid and pd.PIid=pii.PI_id 
and Grnid=@grnid and pii.inst_id=@insid
end

where is problem in this code i am not able to guess please give me suggestion thank's a allot frd
Posted
Updated 9-May-14 2:43am
v3
Comments
Sanket Saxena 9-May-14 8:42am    
Suggesting you to check your sub query it is returning multiple rows that's why this error came.

Its clear from the message what's the issue. One (or more ) of your sub query is returning multiple rows where single row was expected. Its difficult for any boarder to tell the real cause unless we have access to your database. The possible to way to identify is comment all the sub queries and uncomment and see which one is causing a issue. Then you need to modify the query as per your business need such that it return single row. Good luck.
 
Share this answer
 
Comments
King Fisher 9-May-14 9:04am    
clear :)
Further to Solution 1 ... where ArunRajendra is exactly right in describing the approach you should take to finding the problem...

Most of your sub-queries are returning sum of something ... except this one
(Select distinct(volumn) from  Stock_Dispatch  where inst_id=@insid and PIid=@piid and title= pd.title) As Volumn
... which I suspect is the cause of your problem.

Another way you can make life easier for yourself is to use whitespace ... spaces, tabs, newlines ... this is your unaltered code reformatted with some whitespace to make it more readable ...
ALTER procedure [dbo].[track_order1]
@grnid int,
@insid int,
@piid int
as
begin
	Select distinct pii.inst_id, pd.PIid, pd.title,pd.publisher,pd.format ,pd.subscription_period_from as Sub_from,	pd.subscription_period_to as sub_to,pd.Qty_in_year AS Frequency	,
	(
		Select distinct(volumn) 
		from  Stock_Dispatch  where inst_id=@insid and PIid=@piid and title= pd.title
	) As Volumn,
	Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid 
		and title= pd.title and order_Status=2
	) ,0)as Rec_qtys,
	pd.Qty_in_year - Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) 
		from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=2
	) ,0) as rem,
	
	Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) from 
		dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=1
	),0) as New_dispatch,
	case pd.Qty_in_year - Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) 
		from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
		title= pd.title and order_Status=2
	) ,0) 
	when 0 then 'Complete'
	else 'Not Completed' end as Order_stat

	from  
		dbo.Performa_details pd, 
		Client_Goods_details_master po,
		Stock_Dispatch sd 
		right outer join Performa_details pdio on pdio.PIid=sd.PIid,
		Performa_invoice pii 

	where po.PIid=pd.PIid and pd.PIid=pii.PI_id and Grnid=@grnid and pii.inst_id=@insid
end

Once I had done that it was very easy to spot the difference in the sub-queries.
I also notice that you have a mix of methods of joining tables. My advice would be to be consistent - I personally prefer to use ON clauses as it makes it easier to see what the actual filter being used is and also to spot any errors. I had an attempt at your from clause
from
    dbo.Performa_details pd
    inner join Client_Goods_details_master po on po.PIid=pd.PIid
    Stock_Dispatch sd, 
    right outer join Performa_details pdio on pdio.PIid=sd.PIid,
    inner join Performa_invoice pii on pd.PIid=pii.PI_id
    where Grnid=@grnid and pii.inst_id=@insid
and couldn't see how Stock_Dispatch was linked to the other tables (other than Performa_details)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900