Sql Querry

Pravin Ghadge

14y3.2k

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi14y ago
Accepted answer
hi,

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.
0
Pravin Ghadge14y ago
Thanx Jignesh..
Problem has been resolved..
0
Kunal Vaishya14y ago
Try

After Create a String @ConsAcct

do this
EXEC (@ConsAcct)
0
Pravin Ghadge14y ago
Thanx to both for ur reply
@Kunal,
Sorry Kunal i have not understooed what u r trying 2 say.

@Dhaval,
No result. its still giving me same result.
0
Dhaval Patel14y ago
Dear Pravin
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)
0
Kunal Vaishya14y ago
Dear
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
0