Assume you have following table:
Table Name: Emp_Expense
Columns: Emp_Name, Exp_Date, Exp_Type, Exp_Amount, OtherInfo
Now you have to create following SQL to get duplicate rows:
SELECT Emp_Name, Exp_Date, Exp_Type, Exp_Amount, OtherInfo FROM Exp_Expense ee
WHERE 1 < (SELECT COUNT(*) FROM Exp_Expense a
WHERE ee.Emp_Name=a.Emp_Name AND
ee.Exp_Date=a.Exp_Date AND
ee.Exp_Type=a.Exp_Type AND
ee.Exp_Amount=a.Exp_Amount
)
Assume you already have five objects for five columns from table. Create following objects: 1) Object Name: Value_One
Type : Number
SQL : 1
Tables : Emp_Expense
- Object Name: NumberOfExp
Type : Number
SQL : COUNT(*)
Tables : Emp_Expense
While creating report select all five columns. Add object ‘Value_One’ in Conditions panel. Select ‘Less Than’ condition.
Double click on ‘Calculation’ on Operands panel on left. Select ‘NumberOfExp’ object. Click ‘Begin >’ Select Globally. Click ‘Next’
Select Emp_Name, Exp_Date, Exp_Type, Exp_Amount objects. Click ‘Next’
Select atleast one value. click ‘Finish’
Click run.
(I have used Oracle database and BO 4.1.3. Not sure if BO 4.1 supports calculation.)
Hope this helps
– Vasan
A user would like to create a report that shows duplicate expenses that were submitted into an expense system. An expense would be a duplicate if it has the same date, same amount, same expense type and same employee name. I first sort the result set by employee name, date, expense type and
amount in order to get the duplicates together , then I use the Previous() function to identify and flag the duplicates. However, this shows all expenses in addition to the duplicates. Is there a way I can get the report to show only the duplicate expenses and not all expenses?
Any ideas would be greatly appreciated.
Configuration: BO V4.1
Sybase Sql Server 11
Listserv Archives (BOB member since 2002-06-25)