Home » SQL & PL/SQL » SQL & PL/SQL » Ratio_to_report analytical function (Oracle 10.2.0.3)
Ratio_to_report analytical function [message #337693] Thu, 31 July 2008 13:21 Go to next message
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
And what I get is-
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 #337696 is a reply to message #337693] Thu, 31 July 2008 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why didn't you post ALL results and queries between code tags?

Post a test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you get with the same data and why it does fit your requirements.

Regards
Michel
Re: Ratio_to_report analytical function [message #337701 is a reply to message #337696] Thu, 31 July 2008 13:59 Go to previous messageGo to next message
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 #337705 is a reply to message #337701] Thu, 31 July 2008 14:46 Go to previous messageGo to next message
nandinir
Messages: 9
Registered: July 2008
Junior Member
The problem is with the source data. Thats the reason, I was getting multiple rows for the same BU and AID.

Thanks,
Nandini
Re: Ratio_to_report analytical function [message #337757 is a reply to message #337705] Fri, 01 August 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So case is closed?

Regards
Michel
Re: Ratio_to_report analytical function [message #337933 is a reply to message #337757] Fri, 01 August 2008 11:07 Go to previous messageGo to next message
nandinir
Messages: 9
Registered: July 2008
Junior Member
One other question, the percent is giving values upto the 20th place after the decimal point. Can I just print 6 places after the decimal point using ratio_to_report?
Re: Ratio_to_report analytical function [message #337934 is a reply to message #337933] Fri, 01 August 2008 11:11 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can. Use round or to_char depending on how exactly you want it.

Re: Ratio_to_report analytical function [message #337944 is a reply to message #337934] Fri, 01 August 2008 12:13 Go to previous messageGo to next message
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
Re: Ratio_to_report analytical function [message #337946 is a reply to message #337693] Fri, 01 August 2008 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Ratio_to_report analytical function [message #337950 is a reply to message #337693] Fri, 01 August 2008 12:44 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Also beware that RATIO_TO_REPORT wil not round correctly and still add up to 100%. You may need some further processing to cater for rounding (ie. you might be 0.0001 out or something).
Re: Ratio_to_report analytical function [message #337952 is a reply to message #337950] Fri, 01 August 2008 12:46 Go to previous message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you have an example of this?

Regards
Michel
Previous Topic: Case Statement not working in Insert statement
Next Topic: pl sql insert
Goto Forum:
  


Current Time: Tue Dec 06 02:22:15 CST 2016

Total time taken to generate the page: 0.13834 seconds