Home » Developer & Programmer » Reports & Discoverer » dense_rank() in formula column (6i, oracle 10g)
dense_rank() in formula column [message #690198] |
Fri, 29 November 2024 04:47 |
shahzad-ul-hasan
Messages: 639 Registered: August 2002
|
Senior Member |
|
|
i have report with percentage. i want to calculate dense_rank on formula column . My formula column value is
function CF_7Formula return Number is --calculate percentage.
begin
return((nvl(:sumpresent,0)/nvl(:sumwday,0))*100);
end;
Data:
stuid Name Percentage Rank
1 ABC 87.50 want to calculate????
2 FAC 84.20 want to calculate????
3 DAC 85.78 want to calculate????
4 2AC 93.10 want to calculate????
5 HBC 94.58 want to calculate????
6 JA2 79.45 want to calculate????
want to use dense_rank on percentage.
|
|
|
|
|
|
Re: dense_rank() in formula column [message #690206 is a reply to message #690205] |
Mon, 02 December 2024 08:43 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You can't use analytic functions in formula columns, just like you can't use aggregate functions there. You can't because the analytic needs to work across multiple rows and formula columns can only work on one row at a time. To use analytics in reports you would put the analytic in the main report query.
|
|
|
Re: dense_rank() in formula column [message #690215 is a reply to message #690206] |
Mon, 09 December 2024 10:07 |
shahzad-ul-hasan
Messages: 639 Registered: August 2002
|
Senior Member |
|
|
please see the attached file.
Query-1
SELECT sno,to_char(mn,'MONTH-YYYY') mm,stmn.class|| ' ' || stmn.section clss,
' '||stmn.name name,
stmn.stuid,
count(astuid) Absents
,substr(last_day(mn),1,2) Totaldays,studentid,wdays,DA
from (select student.class, student.section,
' '||student.name name,
student.stuid,studentid, da,mn
from (select trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 6 +
least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),6) -
least(dt - trunc(dt,'iw'),6) da,add_months(TRUNC(to_Date(to_char(:yea,'DD-MON-YYYY'), 'DD/MM/YYYY')), rownum-1 ) mn
from (SELECT add_months(to_date(TO_CHAR(:yea,'DD-MON-YY'),'DD/MM/YYYY'),level - 1) dt
FROM dual CONNECT BY LEVEL < 13)),STUDENT
where status='PRESENT') stmn,absent1,months
WHERE absent1.astuid(+)=stmn.stuid
and trunc(absent1.abdate(+), 'MON')=stmn.mn
and class=:cls
and section=:sec
and absent1.abdate(+)>=:yea
and to_char(mon,'MON')=to_char(mn,'MON')
and mon>=:yea
group by
sno,stmn.CLASS|| ' ' ||
stmn.SECTION, stmn.NAME, substr(last_day(mn),1,2) ,stmn.stuid,to_char(mn,'MONTH-YYYY'),to_char(mn,'MM'),studentid,wdays,da
order by sno,to_char(mn,'MM')
Query-2
SELECT t.*, rownum FROM
(select pc,dense_rank() over (partition by stuid order by pc desc nulls last) as pos,stuid,class,section
from (select round((nvl(:sumpresent,0)/nvl(:sumwday,0))*100) As pc,stuid,class,section
from student,absent1
where student.stuid=absent1.astuid
and student.stuid=:stuid
and student.status='PRESENT'
group by stuid,class,section)) t
Rep: 1302 cross product has two or more child group.
[Updated on: Mon, 09 December 2024 10:08] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Dec 12 21:38:21 CST 2024
|