Dear all,
I am trying to get net figure using variable.
declare @ConsAcct as nvarchar(1000)
set @ConsAcct =(select distinct substring((select ''''+ AcctCode + ''',' from oact y where y.fathernum='502000' for xml path('')),0,LEN((select ''''+AcctCode + ''',' from oact y where y.fathernum='502000' for xml path('')))) from oact x)
---this gives me '502100','502200','502300','502400','503000' in @ConsAcct variable
select sum(Debit) from jdt1 inner join oact on jdt1.account=oact.acctcode where
oact.fathernum in (@ConsAcct)
O/P:NULL
but after runnning querry it gives me 'NULL' result
But when i run same query only in place of variable i have hardcoded the no's its give me proper result
select sum(Debit) from jdt1 inner join oact on jdt1.account=oact.acctcode where
oact.fathernum in ('502100','502200','502300','502400','503000')
O/P:54000
I can't understand what may be the reason....
Plz give me some solution
Loading
Jignesh TrivediPosted Apr 24, 2012, 5:37 AM
try,
select sum(Debit) from jdt1 inner join oact on jdt1.account=oact.acctcode where
oact.fathernum in (select SUBSTRING(Value,2,LEN(value)-2) from fn_Split(@ConsAcct,','))
fn_Split is in build function for sql server 2008
but the defination is
ALTER FUNCTION [dbo].[fn_Split]
(
@ListValues nvarchar(max),
@SplitBy nvarchar(5)
)
RETURNS @ResultValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitBy,@ListValues)>0)
Begin
Insert Into @ResultValue (value)
Select
Value = ltrim(rtrim(Substring(@ListValues,1,Charindex(@SplitBy,@ListValues)-1)))
Set @ListValues = Substring(@ListValues,Charindex(@SplitBy,@ListValues)+len(@SplitBy),len(@ListValues))
End
Insert Into @ResultValue (Value)
Select Value = ltrim(rtrim(@ListValues))
Return
END
hope this help.
Pravin GhadgePosted Apr 24, 2012, 7:11 AM
Problem has been resolved..
Kunal VaishyaPosted Apr 24, 2012, 5:08 AM
After Create a String @ConsAcct
do this
EXEC (@ConsAcct)
Pravin GhadgePosted Apr 24, 2012, 4:45 AM
@Kunal,
Sorry Kunal i have not understooed what u r trying 2 say.
@Dhaval,
No result. its still giving me same result.
Dhaval PatelPosted Apr 24, 2012, 3:11 AM
Try below some changes in your query
declare @ConsAcct as nvarchar(1000)
set @ConsAcct =(select distinct substring((select ''''+ AcctCode + ''',' from oact y where y.fathernum='502000' for xml path('')),0,LEN((select ''''+AcctCode + ''',' from oact y where y.fathernum='502000' for xml path('')))) from oact x)
Declare @value as nvarchar(1000)
set @value = Left(@ConsAcct,Len(@ConsAcct)-1)
Print @value
Declare @s varchar(1000)
Set @s ='select sum(Debit) from jdt1 inner join oact on jdt1.account=oact.acctcode where oact.fathernum in ('+@value+')
Exec(@s)
Kunal VaishyaPosted Apr 24, 2012, 2:53 AM
This
SELECT SUBSTRING(
(SELECT ',' + AcctCode
FROM oact y
ORDER BY y.AcctCode
FOR XML PATH('')),2,200000) AS CSV
If Still Problem The AcctCOde Conver IN to Varchar
This is For Varcha