Home » Developer & Programmer » Reports & Discoverer » 5 queries in single report
5 queries in single report [message #215427] |
Mon, 22 January 2007 04:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Assalamo Alaikum...
I have five different types of queries..and I run it at SQL to the get the result...
Can I use these queries to get the output in Single Report....
The queries are as follows...
--- Query for Semester Summary
set serveroutput on
--- 1. -----------Registered Students ----------------
--- Total Students
select count(distinct reg_no) from src
where semester_code=3 and semester_year=2006
/
--- 2. --------Academic Standings Procedure------------
create or replace procedure summary as
cursor c1 is select * from it where sc=3 and sy=2006;
HD number:=0;
H number:=0;
D number:=0;
G number:=0;
S number:=0;
C number:=0;
P number:=0;
N number:=0;
total number;
rem varchar2(200);
begin
for x in c1 loop
if round(x.sgpa,2) >= 3.90 then
HD:=HD+1;
elsif x.sgpa>=3.75 then
H:=H+1;
elsif x.sgpa>=3.50 then
D:=D+1;
elsif x.sgpa>=3.00 then
G:=G+1;
elsif (x.sgpa>1.99 and x.cgpa<1.99) then
C:=C+1;
rem:='Caution';
elsif (x.sgpa>1.99 and x.cgpa >=1.99) then
S:=S+1;
rem:='Satisfactory';
elsif x.sgpa<=1.99 then
P:=P+1;
rem:='Probation';
else n:=n+1;
rem :='Normal';
end if;
end loop;
total:=hd+h+d+g+c+s+p+n;
dbms_output.put_line('-');
dbms_output.put_line('-');
dbms_output.put_line('-');
dbms_output.put_line('Registered Students '||total);
dbms_output.put_line('-');
dbms_output.put_line('-');
dbms_output.put_line('Highest Distinction '||hd);
dbms_output.put_line('Hight Distinction '||h);
dbms_output.put_line('Distinction '||d);
dbms_output.put_line('Good '||g);
dbms_output.put_line('Satisfactory '||s);
dbms_output.put_line('Caution '||c);
dbms_output.put_line('Probation '||p);
dbms_output.put_line('-');
dbms_output.put_line('-');
end;
/
exec summary
/
/*---3..To find total no of students on probation 1,2,3
select count(regno),remarks from(
SELECT REGNO, upper(rpad(name,40)) as name,faculty,SGPA,cgpa, MAX (PROBATION) as remarks
FROM (select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation1' AS PROBATION from it
where sc=3 and sy=2006
and sgpa<=1.995
UNION ALL
select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation2' AS PROBATION from it
where sc=3 and sy=2006
and sgpa<=1.995
and regno in(
select regno from it
where sc=1 and sy=2006
and sgpa<=1.995)
UNION ALL
select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation3' AS PROBATION from it
where sc=3 and sy=2006
and sgpa<=1.995
and regno in(
select regno from it
where sc=1 and sy=2006
and sgpa<=1.995
and regno in(
select regno from it
where sc=3 and sy=2005
and sgpa<=1.995))) gg, grn
where grn.rn=gg.regno
GROUP BY REGNO,name,faculty, SGPA, cgpa)
group by remarks
/
/*--- 4 ---------Courses Offered
select count(distinct course_code) from focd
where semester_code=3 and semester_year=2006
/
select faculty_rt.faculty_initials as faculty,course_type,count(focd.course_code) as courses
from focd,fcp,faculty_rt
where focd.course_Seq_no=fcp.course_seq_no
and focd.faculty_code=faculty_rt.faculty_code
and semester_code=3 and semester_year=2006
group by course_type,faculty_initials
order by 1
/
/*---4 ------------ Student Courses --------------
select count(*) from src
where semester_code=3 and semester_year=2006
/
/*---5 ----- Class Average GPA Query -----------------
select substr(reg_no,1,4) as class,
count(substr(reg_no,1,4)) as st,
sum(gradenumericvalue) as gp,
round((sum(gradenumericvalue))/(count(substr(reg_no,1,4))),2) as avrg,src.tempcolfac
from src,grades_rt
where src.gradecharvalue=grades_rt.gradecharvalue
and semester_code=3 and semester_year=2006
and src.gradecharvalue not in('W','I')
group by substr(reg_no,1,4),src.tempcolfac
order by 1 desc
/
Thanks
RZkhan
|
|
|
|
Re: 5 queries in single report [message #215434 is a reply to message #215427] |
Mon, 22 January 2007 05:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
I created 3 independent queries.
Q_1
select count(distinct reg_no) from src
where semester_code=3 and semester_year=2006
Q_2
select count(distinct course_code) from focd
where semester_code=3 and semester_year=2006
Q_3
select count(*) from src
where semester_code=3 and semester_year=2006
But the output is of each query is on different page.
Can I combine the output on single page.. and also can I show the output of a stored procedure (which runs on SQL) in Reports with the same...plz guide
Thanks
RZKHAN
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Aug 27 09:05:41 CDT 2024
|