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
rzkhan
Messages: 345
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 #215430 is a reply to message #215427] Mon, 22 January 2007 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why not? Create five different, independent queries.
Re: 5 queries in single report [message #215434 is a reply to message #215427] Mon, 22 January 2007 05:07 Go to previous messageGo to next message
rzkhan
Messages: 345
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
Re: 5 queries in single report [message #215447 is a reply to message #215434] Mon, 22 January 2007 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
To put all results onto the same page, you'll have to manually adjust items in Paper Layout. Report Wizard will do the job, but - as you've seen - not exactly as you'd like it to.

Stored procedure as a data source? I've never tried that in Reports; I did, however, use a stored procedure as a source in Forms. Quick glance through Reports Online Help System didn't reveal anything useful. I'm afraid I can't help you.
Re: 5 queries in single report [message #215473 is a reply to message #215434] Mon, 22 January 2007 07:41 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi

That is the better way to solve this problem out of those five queries make one and use that as data source.

Ashu
Re: 5 queries in single report [message #215816 is a reply to message #215427] Wed, 24 January 2007 00:17 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
How can I combine these 5 queries(including 1 stored procedure) as one query. Can I use UNION . or any other idea???

RzKhan
Re: 5 queries in single report [message #215848 is a reply to message #215816] Wed, 24 January 2007 01:31 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You *could* use UNION, but - seeing your queries - this approach doesn't seem to be a good one. Union requires that all queries have the same number and type of selectable columns. As long as you could combine queries 1, 2, 3 and 4 (which select COUNT(*) and possibly one additional column), the fifth one selects several columns which have nothing to do with the first four queries. Of course, you can always select NULL instead of missing columns, but - will this mishmash be the result you expect?

Did you even try to follow suggestion form my previous post?
Re: 5 queries in single report [message #215849 is a reply to message #215427] Wed, 24 January 2007 01:36 Go to previous messageGo to next message
sambaponnekanti
Messages: 9
Registered: January 2007
Location: Hyderabad
Junior Member



Friends


Go to data model in reporst 6i and take "Additional layout tool" s how many quaries if u want to display in Single Report.


Bye
Samba

Re: 5 queries in single report [message #215866 is a reply to message #215427] Wed, 24 January 2007 02:10 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Although I have tried the previous post but..
you'll have to manually adjust items in Paper Layout


I am unable to do this.. plz guide

RzKhan
Re: 5 queries in single report [message #215893 is a reply to message #215866] Wed, 24 January 2007 03:32 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, now read Samba's post. When you identify this button on a toolbar, open Report Builder's Online Help system and learn how to use it.
Re: 5 queries in single report [message #216276 is a reply to message #215427] Fri, 26 January 2007 01:07 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank u..I found the button (in Layout Model) and it did the job. but one problem is that one query out of them is matrix one..I am unable to adjust the items the way I want..sample output is attached. Plz help me adjusting the third query as marix ...thank u

RzKhan
  • Attachment: summary.JPG
    (Size: 72.08KB, Downloaded 148 times)
Re: 5 queries in single report [message #216277 is a reply to message #216276] Fri, 26 January 2007 01:27 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If this query has to be designed as a matrix report, perhaps you should learn how it is done in a separate report (the one which would contain only that query, not "mixed" up with the rest of them). Reports Builder Online Help system contains a nice essay about matrix reports - open it, read it, understand it.

After you figure out how to create a matrix report, implement this knowledge into the "original" report (the one you are now working on).
Re: 5 queries in single report [message #216280 is a reply to message #215427] Fri, 26 January 2007 01:48 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Ok.. I am working on...
Thank you
Previous Topic: Searching Text Query
Next Topic: Request completed with warning
Goto Forum:
  


Current Time: Wed Dec 07 10:47:10 CST 2016

Total time taken to generate the page: 0.19950 seconds