Covert Rows into one column

Sep 10 2014 1:36 AM
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 
 
 
 
 
 
 
 
 

Answers (1)