HOW DO I REMOVE DUPLICATES ROW IN REPORT BUILDER 10G?

Deverouxxx 1 Reputation point
2021-04-15T07:13:51.217+00:00

Please someone help me.
I am facing the problem with the reports. The reports show many duplicates row. Im using REPORT BUILDER 10G.
Here's are my query.
I think there's something wrong with the setting report builder but i dont know

SELECT/*+ ordered */
ad.asset_number || ' - ' || ad.description asset_num_desc,
&ACCT_FLEX_BAL_SEG comp_code,
falu.meaning asset_type,
DECODE (ah.asset_type,
'CIP', cb.cip_cost_acct,
cb.asset_cost_acct) account,
dhcc.segment4 Division,
dhcc.segment6 Depo,
&ACCT_FLEX_COST_SEG cost_center,
ad.asset_number,
ret.date_retired,
th.transaction_type_code,
th.book_type_code,
th.asset_id,
books.date_placed_in_service,
SUM(DECODE(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *
DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) cost,
SUM(DECODE(aj.adjustment_type, 'NBV RETIRED', -1, 0) *
DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) nbv,
/* round(decode(ret.units, null,
(decode(th.transaction_type_code, 'REINSTATEMENT',
-ret.proceeds_of_sale, ret.proceeds_of_sale)
* (dh.units_assigned / ah.units)),
(decode(th.transaction_type_code, 'REINSTATEMENT',
-ret.proceeds_of_sale, ret.proceeds_of_sale)
* nvl(-dh.transaction_units,dh.units_assigned) / ret.units)), 4) proceeds, */
SUM(DECODE(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0) *
DECODE(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *
aj.adjustment_amount) proceeds,
SUM(DECODE(aj.adjustment_type, 'REMOVALCOST', -1, 0) *
DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) removal,
SUM(DECODE(aj.adjustment_type,'REVAL RSV RET',1,0)*
DECODE(aj.debit_credit_flag, 'DR',-1,'CR',1,0)*
aj.adjustment_amount) reval_rsv_ret,
th.transaction_header_id,
DECODE (th.transaction_type_code,
'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
TO_CHAR(NULL)) code
FROM
fa_transaction_headers th,
fa_additions ad,
fa_books books,
fa_retirements ret,
fa_adjustments aj,
fa_distribution_history dh,
gl_code_combinations dhcc,
fa_asset_history ah,
fa_category_books cb,
fa_lookups falu
WHERE
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= :PERIOD2_PCD AND
th.transaction_key = 'R'

AND
ret.asset_id = books.asset_id AND
DECODE (th.transaction_type_code,
'REINSTATEMENT', ret.transaction_header_id_out,
ret.transaction_header_id_in) = th.transaction_header_id
AND
ad.asset_id = th.asset_id
AND
aj.asset_id = ret.asset_id
AND aj.adjustment_type NOT IN (SELECT 'PROCEEDS' FROM fa_adjustments aj1
WHERE aj1.book_type_code = aj.book_type_code
AND aj1.asset_id = aj.asset_id
AND aj1.transaction_header_id = aj.transaction_header_id
AND aj1.adjustment_type = 'PROCEEDS CLR')
AND aj.transaction_header_id = th.transaction_header_id
AND
ah.asset_id = ad.asset_id AND
ah.date_effective <= th.date_effective AND
NVL(ah.date_ineffective, th.date_effective+1)
> th.date_effective
AND
falu.lookup_code = ah.asset_type AND
falu.lookup_type = 'ASSET TYPE'
AND
books.transaction_header_id_out
= th.transaction_header_id AND
books.asset_id = ad.asset_id
AND
cb.category_id = ah.category_id
AND
dh.distribution_id = aj.distribution_id
/* AND (dh.date_effective <= th.date_effective
OR nvl(dh.date_ineffective, th.date_effective+1) >= th.date_effective)
AND th.book_type_code = dh.book_type_code */
AND th.asset_id = dh.asset_id
AND
dhcc.code_combination_id = dh.code_combination_id
GROUP BY
ad.asset_number,
falu.meaning,
&ACCT_FLEX_BAL_SEG,
dhcc.segment4,
dhcc.segment6,
&ACCT_FLEX_COST_SEG,
th.transaction_type_code,
th.book_type_code,
th.asset_id,
cb.asset_cost_acct,
cb.cip_cost_acct,
ad.description,
books.date_placed_in_service,
ret.date_retired,
th.transaction_header_id,
ah.asset_type,
ret.gain_loss_amount
ORDER BY 1,2,3,4,5,6, 7, 8

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,995 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cheong00 3,471 Reputation points
    2021-04-15T07:28:33.423+00:00

    If you don't need the distinct count and just want to remove the duplicates, just use "DISTINCT" keyword after "SELECT" will do.

    However if the result have use case where there should be multiple rows that is exactly the same (probably not in your case because there are fields like date and asset id), you'll want to rewrite the query with SQL92 join syntax (i.e.: [LEFT/RIGHT/FULL/INNER] JOIN <table_name> ON <join condition>) and remove tables one by one to check what cause the extra rows be shown, then maybe make it group-by subquery instead of direct join to table.

  2. ZoeHui-MSFT 32,821 Reputation points
    2021-04-15T09:05:42.433+00:00

    Hi @Deverouxxx ,

    Suppose that you are using Oracle Reports Builder instead of SQL SERVER Reports Builder.

    You may post the issue on Oracle forum for more professional suggestions.

    https://community.oracle.com/hub/

    Regards，
    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    What can I do if my transaction log is full?--- Hot issues November

    How to convert Profiler trace into a SQL Server table -- Hot issues November

    0 comments