August 10, 2009 at 6:29 am
Hi All Iam designing a report by using the following query in sap business one using sql server 2005
the report Format is
ItemName MfrName Batchno Exprdate Quantity Rate vat
Iam getting the report correctly if the item is selected from a single batch no. ididnt get the report if the item is selected from more than one batch no.
it showing the following error
[Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 'User-Defined Values' (CSHS)
my query is like this
declare @sampletab table
(
ItemName varchar(50),
MfrName varchar(50),
Batchno varchar(50),
Exprdate DateTime,
Quantity numeric(3),
Rate numeric(5),
vat numeric(3)
)
declare @ItemNo varchar(25)
declare @ItemName varchar(50)
declare @MfrName varchar(50)
declare @Batchno varchar(50)
declare @Exprdate DateTime
declare @Quantity numeric(3)
declare @Rate numeric(5)
declare @vat numeric(3)
declare @delivno varchar(15)
declare @docno varchar(15)
declare cursorsample cursor for
SELECT T1.[ItemCode],T1.[Dscription], T1.[U_MfrName], T1.[Quantity], T1.[PriceBefDi], T1.[VatPrcnt],T1.[BaseDocNum],T0.[DocNum] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocNum =[%0]
open cursorsample
FETCH NEXT from cursorsample into @ItemNo,@ItemName,@MfrName,@Quantity,@Rate,@vat,@delivno,@docno
while @@FETCH_STATUS=0
begin
select @Batchno=(SELECT distinct T1.[BatchNum] FROM IBT1 T1 WHERE T1.BaseNum=@delivno or T1.BaseNum=@docno)
select @Exprdate=(SELECT distinct T0.ExpDate FROM OIBT T0 Where T0.ItemCode = @ItemNo and T0.BatchNum=@Batchno)
insert into @sampletab
select @ItemName,@MfrName,@Batchno,@Exprdate,@Quantity,@Rate,@Vat
FETCH NEXT from cursorsample into @ItemNo,@ItemName,@MfrName,@Quantity,@Rate,@vat,@delivno,@docno
end
close cursorsample
deallocate cursorsample
select * from @sampletab
thanks in advance
August 14, 2009 at 2:45 pm
select @Batchno=(SELECT distinct T1.[BatchNum] FROM IBT1 T1 WHERE T1.BaseNum=@delivno or T1.BaseNum=@docno)
select @Exprdate=(SELECT distinct T0.ExpDate FROM OIBT T0 Where T0.ItemCode = @ItemNo and T0.BatchNum=@Batchno)
Its because your SELECT statements are returning more than one value to variable.
August 21, 2009 at 7:14 am
hi shahm
thanks for ur reply
iam designing a report by using this following query
declare @sampletab table
(
ItemName varchar(50),
MfrName varchar(50),
Batchno varchar(50),
Exprdate DateTime,
Quantity numeric(3),
Rate numeric(5),
vat numeric(3)
)
declare @ItemNo varchar(25)
declare @ItemName varchar(50)
declare @MfrName varchar(50)
declare @Batchno varchar(50)
declare @Exprdate DateTime
declare @Quantity numeric(3)
declare @Rate numeric(5)
declare @vat numeric(3)
declare @delivno varchar(15)
declare @docno varchar(15)
declare cursorsample cursor for
SELECT T1.[ItemCode],T1.[Dscription], T1.[U_MfrName], T1.[Quantity], T1.[PriceBefDi], T1.[VatPrcnt],T1.[BaseDocNum],T0.[DocNum] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocNum =[%0]
open cursorsample
FETCH NEXT from cursorsample into @ItemNo,@ItemName,@MfrName,@Quantity,@Rate,@vat,@delivno,@docno
while @@FETCH_STATUS=0
begin
select @Batchno=(SELECT distinct T1.[BatchNum] FROM IBT1 T1 WHERE T1.BaseNum=@delivno or T1.BaseNum=@docno)
select @Exprdate=(SELECT distinct T0.ExpDate FROM OIBT T0 Where T0.ItemCode = @ItemNo and T0.BatchNum=@Batchno)
insert into @sampletab
select @ItemName,@MfrName,@Batchno,@Exprdate,@Quantity,@Rate,@Vat
FETCH NEXT from cursorsample into @ItemNo,@ItemName,@MfrName,@Quantity,@Rate,@vat,@delivno,@docno
end
close cursorsample
deallocate cursorsample
select * from @sampletab
the above query retrieves the only single record item which is taken from single batch no
but when the item is selected from more than one batches and if i select the multiple items each one from single batch then iam not getting report it showing the error msg
"1). [Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression. 'User-Defined Values' (CSHS)"
can u suggest the modification in the query to get multiple records of the particular item selected from more than one batch and when multiple items taken
thanks in advance
August 22, 2009 at 12:47 pm
select @Exprdate=(SELECT distinct T0.ExpDate FROM OIBT T0 Where T0.ItemCode = @ItemNo and T0.BatchNum=@Batchno)
Instead of passing values to variable, try storing output of above select statement in a temporary table and
then read values from temporary table.
August 24, 2009 at 12:27 am
hi shahm
thanks for ur reply
i modified the query according to ur suggestions i'm getting the multiple records but i didnt get the batchno. and expirydate for that each item
the main requirement of this qury is batchno. and expiry date
can u suggest where i done mistake in the below query
declare @sampletab table
(
ItemName varchar(50),
MfrName varchar(50),
Batchno varchar(50),
Exprdate DateTime,
Quantity numeric(3),
Rate numeric(5),
vat numeric(3)
)
declare @sampletab1 table
(
btchno varchar(15)
)
declare @sampletab2 table
(
expdate datetime
)
declare @ItemNo varchar(25)
declare @ItemName varchar(50)
declare @MfrName varchar(50)
declare @Batchno varchar(50)
declare @Exprdate DateTime
declare @Quantity numeric(3)
declare @Rate numeric(5)
declare @vat numeric(3)
declare @delivno varchar(15)
declare @docno varchar(15)
declare cursor_sample cursor for
SELECT T1.[ItemCode],T1.[Dscription], T1.[U_MfrName], T1.[Quantity], T1.[PriceBefDi], T1.[VatPrcnt],T1.[BaseDocNum],T0.[DocNum] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocNum =[%0]
open cursor_sample
FETCH NEXT from cursor_sample into @ItemNo,@ItemName,@MfrName,@Quantity,@Rate,@vat,@delivno,@docno
while @@FETCH_STATUS=0
begin
insert into @sampletab1
SELECT distinct T1.[BatchNum] FROM IBT1 T1 WHERE T1.BaseNum=@delivno or T1.BaseNum=@docno
insert into @sampletab2
SELECT distinct T0.ExpDate FROM OIBT T0 Where T0.ItemCode = @ItemNo and T0.BatchNum=@Batchno
insert into @sampletab
select @ItemName,@MfrName,@Batchno,@Exprdate,@Quantity,@Rate,@Vat
FETCH NEXT from cursor_sample into @ItemNo,@ItemName,@MfrName,@Quantity,@Rate,@vat,@delivno,@docno
end
close cursor_sample
deallocate cursor_sample
select * from @sampletab
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply