use the below query -
listagg(employee,',') within group(order by employee),
'([^,]+)(,\1)+' , '\1')
group by department.
If you see duplicates values in your OBIEE analysis, without looking at LISTAGG, you maybe need to check if you don't have a wrong key set somewhere because otherwise OBIEE generally does a distinct and return you aggregates values.
Another thing you can try is maybe to add a fact forcing the aggregation (=> GROUP BY) and so your department and employee become unique and you just hide the aggregated fact column.
Once you solved your duplicates issue you can put back your LISTAGG and it will be fine.
vai if OBIEE costs so much (or at least more than SQL Developer) it's because the tool is a SQL generator, so the idea is not to write full SQL but to model things and let OBIEE do what you paid it for
Thanks for the reply but it's not working, I am still getting duplicate data.
HI Gianni, thanks for the reply, I checked key's are properly set and OBIEE gives distinct record when you don't use LISTAGG, but if you use LISTAGG then it shows duplicate values in the Listagg column.Also I tried the solution you had provied but that's not working, What my expectation from OBIEE is to ceate a subquey to give me distinct records and then place this subquery in the from clause or other solution can be to somehow get the desired result using Regular Expression, the last option is to create a view using below query and then import the view in the RPD but I don't want to do that.
listagg(employee,' & ') within GROUP (
ORDER BY employee)
( SELECT DISTINCT department,
GROUP BY department;
Please let me know your thoughts on the same, thanks.
How did you call the LISTAGG? What does the formula of your column looks like?
Please see below the column formula:
EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',EMP_DEPT.Employee,',',EMP_DEPT.Employee)
1 person found this helpful
After a great deal of research,I found the solution & I want to share this as find it very helpful, we can create an Logical SQL in advanced tab like below and after that you click on New analysis and then you will get your desired results.
SELECT T1.dept saw_0, Evaluate_Aggr('ListAgg(%1,'' & '') Within Group (Order By %1)' as VarChar(1000),T1.emp) saw_1 FROM (
SELECT "emp_dept"."department dept,
FROM "SUBJECT AREA"
GROUP BY dept, emp
) T1 GROUP BY T1.dept ORDER BY saw_0
Also note here that we need to do proper aliasing for parent columns (ex:saw_0,saw_1) , otherwise oracle bi server through error.