| Ratio_to_report analytical function [message #337693] |
Thu, 31 July 2008 13:21  |
nandinir
Messages: 9
Registered: July 2008
|
Junior Member |
|
|
I'm using analytical funtions Ratio_to_report to get the percent of new accounts for a period based on BU and AID
I checked some material online, but unable to replicate the same for my scenario. If it was a salary column then its easy to say -
select
business_unit, asset_id,
RATIO_TO_REPORT(salary) OVER()
from tab1
where period='200808';
But what I have here is a count, and what I'm doing is-
I have a table like this-
BU AID acct_prd .....
101 m 200806 ....
101 p 200806 ....
101 p 200806
103 M 200806
I'm trying to accomplish this in a single step using ratio_to_report-
count(BU) BU AID ACCT_PRD
1 101 M 200806
2 101 P 200806
1 103 M 200806
And I wrote this-
select ratio_to_report(COUNT(BU)) OVER(),
BU, AID
FROM TAB1;
Expected output-
Ratio_to_report BU AID
.25 101 M
.5 101 P
.25 103 M
Ratio_to_report BU AID
.xxx 101 M
.YYY 101 P
.ZZZ 101 P
.WWW 103 M
Looks like there is some syntax in the way I'm pulling data. Any idea?
|
|
|
|
|
|
| Re: Ratio_to_report analytical function [message #337701 is a reply to message #337696] |
Thu, 31 July 2008 13:59  |
nandinir
Messages: 9
Registered: July 2008
|
Junior Member |
|
|
select
RATIO_TO_REPORT(count(0)) OVER(),
c.BU,
l.AID
FROM
TAB1 c
,TAB2 l
where
c.bu = l.bu
c.aid = l.aid
and to_char(a.period, 'YYYYMM')= 200206
group by
c.bu,
l.aid
Output-
RATIO_TO_REPORT BU AID
-------------- ----- ---
.47355164 002 M
.001259446 008 P
.001259446 008 P
[I][B] .002518892 011 M
.007556675 011 M
.001259446 011 P
.017632242 011 P [/B][/I]
.001259446 017 M
.002518892 017 P
.005037783 AER P
.002518892 AMH P
.006297229 AMH P
.021410579 AMP P
.001259446 AMW P
.001259446 APC P
[I][B] .018891688 ATR M
.001259446 ATR P
.005037783 ATR P [/B][/I]
.003778338 BKM M
.002518892 BKM P
.006297229 BKM P
.005037783 BN1 P
This is the actual output. The sql query is the same but I changed the table and column names.
My concern are the records in bold.
[Updated on: Thu, 31 July 2008 13:59] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Ratio_to_report analytical function [message #337944 is a reply to message #337934] |
Fri, 01 August 2008 12:13 |
nandinir
Messages: 9
Registered: July 2008
|
Junior Member |
|
|
I did this and it gives me this error.
SELECT
ROUND(RATIO_TO_REPORT(COUNT(distinct a.acct)),6) OVER (),
a.bu,
substr(a.aid,3,5)
from tab a;
And it gives me this error-
(1): PL/SQL: ORA-30484: missing window specification for this function
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
