ROW DATA REPEATED 34 TIMES IN REPORT BUILDER ORACLE

Question

Hello everyone, i am getting problem for my query.
I've been modified the query and getting duplicates row 34 times.
The value of cost that i get is 55.390.000 AND the value of cost (from original query) is 1.635.000.
If i count 55.390.000 : 1.635.000 = 34 times.

But i dont know which query that makes it duplicates. Please help me. I'll show you my modified query below.

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

Answer 1

and this is the original.
I delete the parameter of book, because thats the request from client. They want to show the all data (not by book)

  SELECT  /*+ ordered */
            &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.segment5           Depo,
     &ACCT_FLEX_COST_SEG cost_center,
     ad.asset_number,
     ret.date_retired,
     ad.asset_number || ' - ' || ad.description asset_num_desc,
     th.transaction_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.book_type_code  =  :P_BOOK AND
     th.transaction_key = 'R'
    AND
     ret.book_type_code = :P_BOOK 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.book_type_code = :P_BOOK
         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.book_type_code = :P_BOOK AND
     books.asset_id = ad.asset_id
    AND
     cb.category_id = ah.category_id AND
     cb.book_type_code = :P_BOOK
    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
     falu.meaning,
     &ACCT_FLEX_BAL_SEG,
     dhcc.segment4,
     dhcc.segment5,
     &ACCT_FLEX_COST_SEG,
     th.transaction_type_code,
     th.asset_id,
     cb.asset_cost_acct,
     cb.cip_cost_acct,
     ad.asset_number,
      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

Answer 2

Suspect the difference is caused by added "ad.asset_number" in GROUP BY clause.

Try remove it and warp ad.asset_number in select statement with MAX().

======

Btw you should really just ask in an Oracle forum as instructed in your previous question. Neither the database server nor the reporting tool you used are from Microsoft.