Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sub Query returns more than one value??

Author  Topic 

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-10-25 : 22:05:32
Good day!

I have a query that gets the running inventory of an item base on a specified date, but Ive noticed that when I change the date value let say from '01-01-2012' to '10-31-2012' it gives me this error.
"Msg 512, Level 16, State 1, Procedure item_quickrpt, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

But when I enter date let say '10-01-2012' to '10-31-2012' it displays the inventory correctly..

And this situation only happens on some items...

My query below:

ALTER PROCEDURE [dbo].[item_quickrpt]
	-- Add the parameters for the stored procedure here
	@itemcode as varchar(max),
	@fromdate as date,
	@todate as date
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
;with stk_card as
(
	select	row_no = row_number() over (partition by prodcode order by podate, stock_in, stock_out),
		    podate, memo, porefno,vendorname,prodcode, itemname, stock_in, stock_out
	from	
	(
		select	podate, memo = 'purchased', porefno,vendorname= (select suppliername from tbl_supplier y
		                                                         where y.suppliercode=v.supplierid),prodcode, itemname, 
		                                                         stock_in = qty, stock_out = 0
		from	PO_detail v
		where	prodcode=@itemcode and (CONVERT(Date, podate) between @fromdate AND @todate)
		
		union all
		
		select	transdate, memo = 'sales', salesid,customername=(select [Last Name] + ', ' + [First Name] + ' ' + [Middle Name] from tbl_costumers l
		                                                         where l.[ID No.]=(select costumerid from tbl_pos_sales_summary c
		                                                         where c.salesid=b.salesid)),productcode, productdesc, 
		                                                         stock_in = 0, stock_out = qty
		from	tbl_pos_sales_detail b
		where	productcode=@itemcode and (CONVERT(Date, transdate) between @fromdate AND @todate)
		
		union all
		
		select	adjustmentdatetime, memo = 'adjustment', [index],userlogin, itemcode, itemname, 
			    stock_in =  case when memo = 'added'    then adjustmenttotal else 0 end, 
			    stock_out = case when memo = 'deducted' then adjustmenttotal else 0 end
		from	tbl_item_quantity_adjusments
		where	itemcode=@itemcode and (CONVERT(Date, adjustmentdatetime) between @fromdate AND @todate)
	) t
)
select	*
from	stk_card s
	cross apply
	(
		select	remaining_qty = sum(isnull(stock_in, 0) - isnull(stock_out, 0))
		from	stk_card x
		where	x.prodcode=@itemcode
	    and	x.row_no	<= s.row_no
	) r
END


I suspect the red color on the code might be the error but I don't know what exactly the error occurs!

Thank you for helping!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-26 : 06:43:13
When you change the date range, the inner queries (the ones you have shown in red returns more than one row, which is the problem.

You can mask the problem by adding a TOP 1 clause into each of those subqueries, as shown below. However, you may only be masking the problem. The question you have to answer from a business logic perspective is a) why is it returning more than one suppliername, and b) if it is, how should you process that information.

		select	podate, memo = 'purchased', porefno,vendorname= (select TOP 1 suppliername from tbl_supplier y
		                                                         where y.suppliercode=v.supplierid),prodcode, itemname, 
		                                                         stock_in = qty, stock_out = 0
....
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-10-27 : 07:19:39
thank you sunitabeck!

Ive modified as to what you've suggested but it still gives me the same error!


ALTER PROCEDURE [dbo].[item_quickrpt]
	-- Add the parameters for the stored procedure here
	@itemcode as varchar(max),
	@fromdate as date,
	@todate as date
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
;with stk_card as
(
	select	row_no = row_number() over (partition by prodcode order by podate, stock_in, stock_out),
		    podate, memo, porefno,vendorname,prodcode, itemname, stock_in, stock_out
	from	
	(
		select	podate, memo = 'purchased', porefno,vendorname= (select TOP 1 suppliername from tbl_supplier y
		                                                         where y.suppliercode=v.supplierid),prodcode, itemname, 
		                                                         stock_in = qty, stock_out = 0
		from	PO_detail v
		where	prodcode=@itemcode and (CONVERT(Date, podate) between @fromdate AND @todate)
		
		union all
		
		select	transdate, memo = CASE WHEN b.salestype='cash' THEN 'cash sales' ELSE 'credit sales' END, 
		        salesid,customername=(SELECT [Last Name] + ', ' + [First Name] + ' ' + [Middle Name] from tbl_costumers l
		                              where l.[ID No.]=(select costumerid from tbl_pos_sales_summary c
		                              where c.salesid=b.salesid)),productcode, productdesc, 
		                                                         stock_in = 0, stock_out = qty
		from	tbl_pos_sales_detail b
		where	productcode=@itemcode and (CONVERT(Date, transdate) between @fromdate AND @todate)
		
		union all
		
		select	adjustmentdatetime, memo = 'adjustment', [index],userlogin, itemcode, itemname, 
			    stock_in =  case when memo = 'added'    then adjustmenttotal else 0 end, 
			    stock_out = case when memo = 'deducted' then adjustmenttotal else 0 end
		from	tbl_item_quantity_adjusments
		where	itemcode=@itemcode and (CONVERT(Date, adjustmentdatetime) between @fromdate AND @todate)
	) t
)
select	*
from	stk_card s
	cross apply
	(
		select	remaining_qty = sum(isnull(stock_in, 0) - isnull(stock_out, 0))
		from	stk_card x
		where	x.prodcode=@itemcode
	    and	x.row_no	<= s.row_no
	) r
END

Thank you!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-27 : 08:51:01
I was only showing an example. You have to insert a similar TOP 1 clause in the second subquery you had shown in red in your original post.

I want to reiterate that adding the TOP 1 clause is really only masking the problem rather than solving it.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-10-27 : 09:08:14
thanks sunitabeck!

Ive also did TOP 1 on the second sub query but the error msg is still showing..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-27 : 09:20:16
If you have TOP 1 clause in all 3 of your subqueries, that should not happen - unless there are some other similar subqueries. If you double-click on the error in the output window, it will take you to the line where the error is happening.
select	podate, memo = 'purchased', porefno,vendorname= (SELECT TOP 1 suppliername from tbl_supplier y
                                                         where y.suppliercode=v.supplierid),prodcode, itemname, 
                                                         stock_in = qty, stock_out = 0
from	PO_detail v
where	prodcode=@itemcode and (CONVERT(Date, podate) between @fromdate AND @todate)

union all

select	transdate, memo = 'sales', salesid,customername=(select TOP 1 [Last Name] + ', ' + [First Name] + ' ' + [Middle Name] from tbl_costumers l
                                                         where l.[ID No.]=(select TOP 1  costumerid from tbl_pos_sales_summary c
                                                         where c.salesid=b.salesid)),productcode, productdesc, 
                                                         stock_in = 0, stock_out = qty
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-10-27 : 10:00:58
thank you sunitabeck!

It seems to be working but Ive got sales with no customer name on it. How to display and allow null customer name because error occurs if it reaches on Null Customer name.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-27 : 14:36:11
I have not understood the business logic you are trying to implement, so I am unable to suggest what you need to do. If you can post some sample data in your tables and required output data people on the forum may be able to offer suggestions.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-10-28 : 22:53:10
Thank you sunitabeck!

Its working already, I see that NULL are those sales with no specified customer..

Im just worried why the query is running slow especially when the date being query is with a long gap. Example from '01-01-2012' to '10-31-2012' especially those items that have the highest sales. Is it because of the sub query? Or is it because of my table structure? Purchases table is separated from Sales table(maybe this part makes it slow)? How to make it more fast?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-29 : 08:58:43
You have to look at the execution plan to see what parts of it is taking up the resources. Once you identify that, it may be possible to take steps to speed it up.

Also, make sure that your database is properly maintained - talk to your DBA to make sure that statistics are regularly updated and fragmented indexes are being rebuilt or reorganized.

Without knowing much more about your query or tables, it is hard for me to say anything more specific. One thing I would suggest though, is the following. In 3 or four places, you have a where clause that involves dates. For example:
(CONVERT(Date, podate) between @fromdate AND @todate)
Change that to:
podate >= @fromdate AND podate < DATEADD(dd,1,@todate)
I am showing only an example. You have to do that in every place.

That may or may not help. If there is an index on the date columns making this change will allow optimizer to use that index.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-10-29 : 11:06:01
Thank you sunitabeck!

Ive changed the date format as you have suggested but still it took 1:49 minutes for the query to display the result. By the way the tbl_pos_sales_detail where all sales transactions are saved has no index at all. So Ive create a nonclustered index as below:

CREATE NONCLUSTERED INDEX SalesIndex
ON tbl_pos_sales_detail  (productcode)

Now it loads at 00:19. Is the index Ive created exactly make it a little faster? Should I create a nonclustered index to PO_detail(where all purchases detail are saved) too for productcode?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-29 : 11:33:52
Possible candidates:

On tbl_pos_sales_detail: productcode and transdate
On po_detail: PO_detail: podate, podate
On tbl_item_quantity_adjusments: itemcode, adjustmentdatetime

But, I am guessing - you should take a look at the query plan, and see which tables are taking up more resources. Creating too many indexes can be counter productive.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-10-30 : 19:39:25
Thank you sunitabeck!

Ive created nonclustered index for podate,prodcode,qty in PO_detail, and also Ive create index for salesid,productcode,qty in tbl_pos_sales_detail. Now it loads at 00:04. Ive seen at the execution plan that the highest percentage of query usage is within this code (usage 30%):

....
where productcode=@itemcode and transdate >= @fromdate AND transdate < DATEADD(dd,1,@todate)
....


Thank you!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site