Hi,
I have table sales like
ID Sales_Value active
1 25 0
2 35 1
3 2 0
4 18 0
I need to fetch all active Values in a column to use in where condition in another query. like below
Sales_Value IN (25,2,18)
for this i used below statement
SELECT REPLACE (RTRIM (XMLAGG (XMLELEMENT (e, Sales_Value || ',')).EXTRACT ('//text()').EXTRACT ('//text()'),','),',',',') SalesResult
FROM sales S
WHERE S.IS_DELETE = 0
this is giving result like 25,2,18. issue is when i use this query as sub query in where condition i am getting error like
"invalid number" at REPLACE
1 Reply
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Jignesh TrivediPosted Sep 10, 2014, 2:18 AM
Hi,
With SQL server 2008, there is one in build function called fn_split.
this function split your value and create function..
you can use this function here
Sales_Value IN (select value from from fn_Split('25,2,18',','))
please refer
http://technet.microsoft.com/en-us/library/aa496058(v=sql.80).aspx
hope this will help you.