Shalu Aggarwal, November 08, 2002 - 2:03 pm UTC
Hi, When I tried running my query after making the changes I am getting the following error:
ORA-30483 Window functions are not allowed here.
I am using this query in Reports Builder.
In case you want to take a look at my query it is here, you can just check the WEIGHTED_PERCENTILE column.
Select
c.name,
nvl(c.upb,0) upb,
nvl(abs(c.upb),0) abs_upb,
Power( nvl(c.upb,0) , 2) sqr_upb,
nvl(c.upb,0) * ( NVL(&p_col1,0) - NVL(&p_col2,0)) mean_diff,
ABS( nvl(c.upb,0) * ( NVL(&p_col1,0) - NVL(&p_col2,0) ) ) mean_abs_diff,
POWER ( nvl(c.upb,0) * ( NVL(&p_col1,0) - NVL(&p_col2,0) ), 2) root_mean_sqr_diff,
nvl(c.upb,0) * CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE round(((&p_col1 - nvl(&p_col2,0) )/&p_col1),6) END mean_diff_per,
ABS( nvl(c.upb,0) * CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE round(((&p_col1 - nvl(&p_col2,0) )/&p_col1),6) END ) mean_abs_diff_per,
POWER ( nvl(c.upb,0) * ( CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE round(((&p_col1 - nvl(&p_col2,0) )/&p_col1),6) END ), 2) root_mean_sqr_diff_per,
c.product_type product_type,
c.ORIGINAL_TERM original_term,
c.wam wam,
c.wac wac ,
c.coupon coupon,
c.cusip,
( Sum(nvl(c.upb,0)) OVER ( ORDER BY ABS( CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE ((&p_col1 - nvl(&p_col2,0) )/&p_col1) END) ) -
Sum(nvl(c.upb,0) OVER ( ORDER BY ABS( CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE ((&p_col1 - nvl(&p_col2,0) )/&p_col1) END) RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING) ) /SUM(nvl(c.upb,0)) WEIGHTED_PERCENTILE,
NVL(&p_col1,0) - NVL(&p_col2,0) diff,
ABS( NVL(&p_col1,0) - NVL(&p_col2,0) ) abs_diff,
ABS( CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE ((&p_col1 - nvl(&p_col2,0) )/&p_col1) END ) abs_diff_per,
CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE round(((&p_col1 - nvl(&p_col2,0) )/&p_col1),6) END diff_per,
decode(&p_col1, 0, 'NaN', null, 'Missing', &p_col1) ref_col,
decode(&p_col2 ,0, 'NaN', null, 'Missing', &p_col2) test_col
From VN_INSTRUMENT c, VN_RESULT a, VN_RESULT b
Where
c.instr_id = a.instr_id and
a.instr_id = b.instr_id and
a.group_id = 645 and
b.group_id = 655
Order By ABS( CASE WHEN nvl(&p_col1,0) = 0 Then 0 ELSE ((&p_col1 - nvl(&p_col2,0) )/&p_col1) END )
November 08, 2002 - 2:20 pm UTC
did you try it in sqlplus -- you would need to give me a test case (create table statement and as SMALL of a query as humanly possible).
be glad to try and help you -- however, you have to make it easy for me to do so.
Shalu Aggarwal, November 08, 2002 - 3:28 pm UTC
Hi,
I figured it out. It was not working at my end because I had NVLs in my query. When i removed all the NVLs it started working, otherwise on SQL Plus also I was getting the same ORA error.
Thanks so much for your help, it is really great. But I would like to know few things:
1. Does it take care of NULLs ?
2. What will it do if sum(upb) in the denominator for some reason is null or zero ?
3. Can I use CASE statement inside it ?
Thanks,
Shalu
November 08, 2002 - 3:38 pm UTC
you can "order by whatever NULLS LAST" or "NULLS FIRST" to control where they might pop up. In general -- the functions ignore nulls -- sum() will for example.
If the sum(upb) is NULL or ZERO -- you'll get an error -- so just decode or CASE around that problem.
I don't know -- inside "what" exactly?
Shalu Aggarwal, November 08, 2002 - 3:41 pm UTC
Hi,
I am getting the same ORA error if I use CASE statement or NVL inside it. I need to use CASE or NVL to make sure that 1.) denominator is never zero
2.) a.oad is never zero since I am dividing (a.oad-b.oad) by a.oad.
Look at the following query sample: WEIGHT column works fine here but not WEIGHT1, actually I want to use it as I have used for WEIGHT1 column to handle nulls and zeros.
Select
c.name,
nvl(c.upb,0) upb,
( Sum(c.upb) OVER ( ORDER BY ABS( (a.oad - b.oad)/a.oad) ) -
Sum(c.upb) OVER ( ORDER BY ABS( (a.oad - b.oad)/a.oad)
RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING) ) /SUM(c.upb) OVER ( ) WEIGHT,
( Sum(c.upb) OVER ( ORDER BY ABS( CASE WHEN a.oad = 0 Then 0 ELSE ((a.oad - b.oad )/a.oad) END )
Sum(c.upb) OVER ( ORDER BY ABS( CASE WHEN a.oad = 0 Then 0 ELSE ((a.oad - b.oad )/a.oad) END )
RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING) ) /SUM(c.upb) OVER ( ) WEIGHT1,
ABS( (a.oad - b.oad)/a.oad) abs_diff_per
From VN_INSTRUMENT c, VN_RESULT a, VN_RESULT b
Where
c.instr_id = a.instr_id and
a.instr_id = b.instr_id and
a.group_id = 654 and
b.group_id = 655
Order By ABS( CASE WHEN nvl(a.oad,0) = 0 Then 0 ELSE ((a.oad - nvl(b.oad,0) )/a.oad) END )
Shall await your reply.
Thanks!
Shalu
November 08, 2002 - 4:57 pm UTC
you () are totally messed up -- but I have no problems using decode with analytics:
ops$tkyte@ORA920.US.ORACLE.COM> select * from t;
INSTR_ID UPB CUSIP OAD1 OAD2
---------- ---------- ---------- ---------- ----------
10 500 cusip1 .03 .01
20 100 cusip2 .09 .04
30 200 cusip3 .03 0
40 200 cusip4 .05 .02
50 200 cusip5 .09 .07
60 400 cusip6 .05 .03
60 400 cusip6 0 0
7 rows selected.
ops$tkyte@ORA920.US.ORACLE.COM> Select
2 instr_id,
3 nvl(upb,0) upb,
4 (Sum(upb) OVER ( ORDER BY ABS( decode( oad1, 0, 0, (oad1 - oad2)/oad1)) ) -
5 Sum(upb) OVER ( ORDER BY ABS( decode(oad1, 0, 0, (oad1 - oad2)/oad1))
6 RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING) ) /
7 decode(SUM(upb) OVER (), 0, 0, sum(upb) over()) WEIGHT1,
8 ABS( decode(oad1, 0, 0, (oad1 - oad2)/oad1)) abs_diff_per
9 From t
10 Order By ABS( CASE WHEN nvl(oad1,0) = 0 Then 0 ELSE ((oad1 - nvl(oad2,0) )/oad1) END )
11 /
INSTR_ID UPB WEIGHT1 ABS_DIFF_PER
---------- ---------- ---------- ------------
60 400 0 0
50 200 .2 .222222222
60 400 .3 .4
20 100 .5 .555555556
40 200 .55 .6
10 500 .65 .666666667
30 200 .9 1
7 rows selected.
A reader, November 08, 2002 - 3:59 pm UTC
I tried using decode, NVL and CASE statement, it gives same ORA error.
See if use decode as follows, I get the same error:
Select
c.name,
nvl(c.upb,0) upb,
( Sum(c.upb) OVER ( ORDER BY ABS( decode(a.oad,0,0, (a.oad - b.oad )/a.oad ) -
Sum(c.upb) OVER ( ORDER BY ABS( decode(a.oad,0,0, (a.oad - b.oad )/a.oad )
RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING) ) /SUM(c.upb) OVER ( ) WEIGHT
From VN_INSTRUMENT c, VN_RESULT a, VN_RESULT b
Where
c.instr_id = a.instr_id and
a.instr_id = b.instr_id and
a.group_id = 654 and
b.group_id = 655
Order By ABS( CASE WHEN nvl(a.oad,0) = 0 Then 0 ELSE ((a.oad - nvl(b.oad,0) )/a.oad) END )
/
It is not allowing any CASE/NVL/decode, is there any other alternative ??
A reader, November 08, 2002 - 5:36 pm UTC
Really. It's working fine now.
Thanks, so much!!
Shalu
Similar usage of percentile_disc in oracle 8i
Vivek Sharma, April 13, 2004 - 7:02 am UTC
Dear Tom,
I was going through your book "Effective Oracle by Design" and in Chapter 3 you had demonstrated a usage of percentile_disc package available in Oracle 9i. Is this funcationality available in Oacle 8i. We have some big batch processes which can be parallelized using the logic as demonstrated by you but we have an Oracle 8i Database. Is there any package similar to percentile_disc available in Oracle 8i ? if yes, can you please demonstrate the usage. It will definetely help me improve the response time of some big processes.
Thanks and Regards
Vivek Sharma
April 13, 2004 - 8:13 am UTC
read down a paragraph where it states "in chapter 8, we'll take another look at DIY parallelism. You'll see we can accomplish the same goal using ROWID ranges which are even more efficient than the index key approach shown here"...
look at chapter 8