question

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

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

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-supported
· 3
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 , are you using sql server or oracle or any other datasource? If you are using sql server as datasource, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

0 Votes 0 ·

Hi Zoehui thankyou for your respones.

Im using TOAD for oracle for compile the query , and Report Builder 10g for modified the report

0 Votes 0 ·

Most likely Oracle database because of the DECODE() function in SQL statement.

I choose to answer that solely because the way to solve would be the same in both SQL environment.

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

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.

· 3
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.

Hi cheong00 thankyou for your response.
I've tried it and in oracle there's no showing duplicate data. But, when I request report output in Report Builder 10g, the result showing duplicates row

0 Votes 0 ·

Try change some of the fields in query to literal string to make sure you really updated the query. (Say, try make comp_code to be 'ABC')

Also, if you're using this in subreport and the subreport part got repeated, check how you link between the reports.

0 Votes 0 ·

Hi cheong00, I've found something.
This query (which I post) has been modified by me, and I saw the ORIGINAL (hasn't modified ) query that the value of cost is 1.635.000,
and the value of cost that i've been modified become 55.590.000.

Its repeated 34 times.
But I dont know which wrong with my query

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @Deverouxxx-0847,

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

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.