Hi All,
I am trying to create a dataset on BIRT 2.3.0 and database is Oracle 10g and I have to first put multiple rows value in one single row and as it may contain duplicate values, I need to remove the duplicate values as well.
The sample of the string formed after grouping multiple rows value in one single row will be like - 1,2,4,5,3,4,90,67,90,67,56,56,56 and so on. And the output should be 1,2,3,4,5,56,67,90.
So the query I am using is :-
WITH filtered_data AS
(select /*+materialize*/ substr(replace (rtrim (xmlagg (xmlelement (e, <column_name> || '')).extract ('//text()'), ','),'/',','),2) all_data
from <table_name>
)
select rtrim(str_new,',') unique_data, all_data from filtered_data
model
dimension by (0 dim)
measures(all_data, all_data||',' str_new)
rules iterate(100) until (str_new[0] = previous(str_new[0]))
(str_new[0]=regexp_replace(str_new[0],'(^|,)([^,]+,)(.*?,)?2+','123'));
Note - Replace <table_name> and <column_name> with actual values.
Query runs perfectly fine on any sql plus with the result I am hoping. But when I deploy the same on BIRT dataset, it gives error "invalid character".
Does BIRT supports regexp parsing? Has anyone faced similar problem before? What could be the solution?
Regards,
Ashish
