Oracle 9i SQL for CR-XI (Distinct Count within Grouping) 2

[MJRBIM]

I'm trying to debug someone elses ORACLE 9i SQL for a CR-XI report - and my PL/SQL needs some dusting off (it's been a while).

The SQL below is giving bad results for the two COUNT values for recipient_id and arr_id - and the Sum(ba.calculated_amount).

I think that the COUNTS actually need to be DISTINCT COUNTS at the "FY-and-REGION" level, but the current SQL is generating a COUNT (of all non-Null values) rather than a DISTINCT and only at the FY level.

The SUM needs to be a SUM at the "FY-and-REGION" level, but the current SQL is generating a SUM only at the FY level.

===========

select fy.fy_long fiscal_year,
reg.region_ename,
count(r.recipient_id),
count(arr.arr_id),
Sum(ba.calculated_amount)
from abcd.arrangement arr,
abcd.arr_transaction_activity ata,
abcd.budget_allocation ba,
abcd.mv_region reg,
abcd.fiscal_year fy,
abcd.recipient r
where ata.arr_id = arr.arr_id
and ba.trg_arr_tx_act_id = ata.arr_tx_act_id
and ba.fy_short = fy.fy_short
and r.recipient_id = arr.recipient_id
group by fy.fy_long, reg.region_ename

===========

If anyone has some advice on DISTINCT COUNTS and HIERARCHICAL GROUPING in PL/SQL it would be appreciated...

Thanks in advance....

 

[IanWaterman]

DISTINCT COUNTS are straight forward

eg
count(distinct(arr.arr_id)),

Hierarchical tables are more difficult, you will need to alias the table back in to sort out the hierachy. You may even need a subquery to break out your hierachy, and then add that into your query above as an inline query.

Without knowing about your hierachy structure its difficult to be explicit.

Ian

 

[IdoMillet]

Doesn't sound like a hierarchy issue.

The statement you have should indeed produce aggregates at the level of FY & Region (not just FY).

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MJRBIM]

Thanks for the DISTINCT count tip!

I think my other aggregate issue is actually a linking problem - or an issue with the migrated data.

Results are dividing the Sum(ba.calculated_amount) equally across all REGIONS, and showing me the same DISTINCT counts for all REGIONS.

Thanks for the help.

 

[IdoMillet]

Are you looking at the raw data in the detail section of the Crystal report or are you doing some sort of aggregation in Crystal?

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MJRBIM]

No aggregation in Crystal (display tool only) - the Oracle SQL will eventually be part of a Stored Procedure.

I'm going to sit-down with a DBA who worked on migrating this data into Oracle. I think it's a bad migration, or a bad link in the SQL joins.

 

[IdoMillet]

You have 6 tables and only 4 joins. You should have at least 5 joins in order to span 6 tables.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MJRBIM]

Thanks Ido, I will look into that....