Hi ALL,
Im running a report in webi and it returns all double rows in the result so by that insted of showing 2 claim count it showing 8 because there are 4 duplicated (4*2= 8 ).
If i manually put distinct after select statement in webi SQL behind its working can i put automatically, i have one table TableA which is a summary table and any join with that table brings duplicate records.
How can i avoid this?Please let me know.
Thanks in advance,
Madhu.
hi,
i think u r missing one of the joins.
bâcoz i had this kind of issue one time and i was missing one join.so check all the joins and try it again.
Hi Biku724,
Please do not use Instant Message abbreviations (u, r, bâcoz) on BOB. We want to keep BOB easy to understand for our International community. Thanks.
Hi Thankyou so much for quick response,
Well do you want me to check the join between the summary table and the other table? Between what tables?
Here TableA which is summary table is by Date means every thing will be same except the ExpDate.
means
col1 col2 ...... ExpDate
1 A 1/1/2007
1 A 1/2/2007
1 A 1/3/2007
1 A 1/4/2007
.
.
.
.
2 B 2/1/2007
2 B 2/2/2007.
Any join with this table will be repeted these number of time.
Hi,
Well i just checked with DBA the join between those 2 tables should be that way the Summary table is in such a way that it will record the transaction on Date basis with same set of claims so claims are repeting with different ExpDate. No other join can be possible between them.
You may very well have a data model issue. However, you can add the distinct by opening the query properies and marking the âNo Duplicate Rowsâ option. That will add the distinct.
Note that this doesnât fix a data model problem, it may very well just mask it. But to answer your question, thatâs how you can get the word âdistinctâ included in your query.
Hi Dave,Thank you so much.
Thats good enough,they basically want to mask DB errors thats what i observed,so this will be good enough.
Can you please tell me do we have to do this for every report how to set this by default for evry report? If they open a new report the option should be unchecked,how to do this please let me know.
Hi Good Morning,
In the report the querry option for duplicates is always checked, if i want to uncheck for every new reort to be created what i have to do ? please let me know.
:?
Thanks,
Madhu.
You canât, and frankly that would be a bad idea anyway.
Distinct has overhead, makes your query run slower. If you require distinct on every query then you have data problems that need to be resolved. Adding a distinct doesnât fix the problem.
Agreed, sort the data model out at the source, this sort of Bodging / fixing has no place in the presentation layer.
Usually, it has an eventual cost, often it causes problems not only for users, but also for maintenance - if someone, such as yourself, leaves the project.
If you reports are complex, you are going to be seeing bad performanceâŚ
To be Frank Dave,
They dnt want to change the data in the database,we have 3 objects, DateLag1,DateLag2,DateLag3 well this will caliculate the number of days between two dates,now as dates are repeting for suppose number of days between is 4 and as per querry the rows are duplicating for 4 times the days that are returning are 4*4 = 16 which is wron,if i create a variable in the report its showing correct(that because the already existing DateLag1 field is making join 4times so its repeting). Now they want me to fix the DateLag1,DateLag2âŚThese date fields, so that even thoug its repeting they have to show up coorect date.They are universe objects,i dnt know what to do that .Their definition is:
DateLag1= To_Date(DateRetuned,âYYYY/MM/DDâ) - To_Date(DateReported,âYYYY/MM/DDâ).(This is declared as measure in universe)
Please help me out with thisâŚ
HI ALL,
I got the solution ,i wanted to share with you all,well i did not fixed the perminent solution as they dnât want to touch the DB, but i fixed the variables,
They have been declared as measures, and in the property it is âFunction to be used when Aggregatedâ(Option in Object Properties when we select the Qualification of object) is âSUMâ before, now i have changed to âMAXâ.
That worked as of now, but i still support your discussion always remove the bug from the root. Thank You ALL for helping me.I appreciate your help.
Thanks again,
Madhu.
(BOB member since 2003-11-27)
(BOB member since 2002-06-06)
(BOB member since 2005-01-06)