question

Deverouxxx-0847 avatar image
0 Votes"
Deverouxxx-0847 asked Deverouxxx-0847 commented

ROW DATA REPEATED 34 TIMES IN REPORT BUILDER ORACLE

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


not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Deverouxxx-0847 avatar image
0 Votes"
Deverouxxx-0847 answered Deverouxxx-0847 published

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cheong00 avatar image
0 Votes"
cheong00 answered Deverouxxx-0847 commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i've ask in oracle forum and still dont get reply.


UPDATED
BTW, i've found why the value cost 55.590.000

BUT the output of report still duplicate in reports builder

0 Votes 0 ·