Home » SQL & PL/SQL » SQL & PL/SQL » Different Types of reports
Different Types of reports [message #287869] Thu, 13 December 2007 21:34 Go to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
i create the following tables ,can you tell me how many different types of report
can i prepare from
these tables please send your feed back as soon as possible

CREATE TABLE "HRMTEST"."JOB_ADVERTISE" ("ADVERTISE_ID"
NUMBER(10) NOT NULL, "ADVERTISE_NO" VARCHAR2(60 byte),
"ADVERTISE_DATE" DATE NOT NULL, "DESIG_ID" VARCHAR2(5 byte)
NOT NULL, "NO_OF_VACANCIES" NUMBER(10), "DEPT_SEC_ID"
VARCHAR2(10 byte), "PAYSCALE_ID_PK" VARCHAR2(2 byte),
"CLOSING_DATE" DATE,
CONSTRAINT "FK_DEPT_SEC_ID_1" FOREIGN KEY("DEPT_SEC_ID")
REFERENCES "HRMTEST"."DEPARTMENT_SECTION"("DEPT_SEC_ID"), 0
CONSTRAINT "FK_DESIG_ID_1" FOREIGN KEY("DESIG_ID")
REFERENCES "HRMTEST"."DESIGNATION"("DESIG_ID"),
CONSTRAINT "PK_ADVERTISE_ID_1" PRIMARY KEY("ADVERTISE_ID"),
CONSTRAINT "UK_ADVERTISE_1" UNIQUE("ADVERTISE_DATE",
"DESIG_ID", "DEPT_SEC_ID") )


CREATE TABLE "HRMTEST"."JOB_CANDIDATES" ("CANDIDATE_ID"
NUMBER(10) NOT NULL, "NAME" VARCHAR2(40 byte) NOT NULL,
"FATHER_NAME" VARCHAR2(40 byte), "ADVERTISE_ID" NUMBER(10)
NOT NULL, "EMAIL" VARCHAR2(50 byte), "CONTACT_NO" VARCHAR2(10
byte) NOT NULL, "POSTAL_ADDRESS" VARCHAR2(100 byte), "DOB"
DATE, "NIC" VARCHAR2(15 byte), "ACCEPTED_IN_SCRUTINY" CHAR(1
byte), "TEST_DATE" DATE, "TEST_STATUS" VARCHAR2(10 byte),
"TEST_REMARKS" VARCHAR2(100 byte), "CANDIDATE_NO" VARCHAR2(20
byte), "SCRUTINY_REMARKS" VARCHAR2(100 byte), "TITLE"
VARCHAR2(10 byte), "GENDER" CHAR(1 byte),
"TEST_LETTER_ISSUED" CHAR(1 byte), "TEST_LETTER_ISSUE_DATE"
DATE, "INTERVIEW_LETTER_ISSUED" CHAR(1 byte),
"INTERVIEW_LETTER_ISSUE_DATE" DATE,
"APPOINTMENT_LETTER_ISSUED" CHAR(1 byte),
"APPOINTMENT_LETTER_ISSUE_DATE" DATE,
CONSTRAINT "FK_ADVERTISE_ID_1" FOREIGN KEY("ADVERTISE_ID")
REFERENCES "HRMTEST"."JOB_ADVERTISE"("ADVERTISE_ID"),
CONSTRAINT "PK_CANDIDATE_ID_1" PRIMARY KEY("CANDIDATE_ID") )


CREATE TABLE "HRMTEST"."JOB_EXPERIENCE" ("CANDIDATE_ID"
NUMBER(10) NOT NULL, "ORGANISATION" VARCHAR2(100 byte) NOT
NULL, "POST" VARCHAR2(50 byte), "GRADE" VARCHAR2(8 byte),
"DATEFROM" DATE, "DATETO" DATE, "DESCRIPTION" VARCHAR2(200
byte),
CONSTRAINT "FK_CANDIDATE_EXP_1" FOREIGN KEY("CANDIDATE_ID")
REFERENCES "HRMTEST"."JOB_CANDIDATES"("CANDIDATE_ID"))


CREATE TABLE "HRMTEST"."JOB_LETTERS" ("LETTER_ID" NUMBER(10)
NOT NULL, "LETTER_NO" VARCHAR2(100 byte) NOT NULL,
"LETTER_DATE" DATE NOT NULL, "CANDIDATE_ID" NUMBER(10) NOT
NULL, "SUBJECT" VARCHAR2(700 byte) NOT NULL, "LETTER_TYPE"
VARCHAR2(30 byte) NOT NULL)




CREATE TABLE "HRMTEST"."JOB_POST_OFFERED" ("POST_OFFERED_ID"
NUMBER(10) NOT NULL, "DESIG_ID" VARCHAR2(5 byte) NOT NULL,
"DEPT_SEC_ID" VARCHAR2(3 byte), "PAYSCALE_ID_PK" VARCHAR2(2
byte), "INDUCTION_ID" VARCHAR2(2 byte) NOT NULL,
"OPERATION_ID" VARCHAR2(2 byte) NOT NULL, "CANDIDATEID"
NUMBER(10) NOT NULL, "JOB_SB_CANDIDATE_ID" NUMBER(10) NOT
NULL,
CONSTRAINT "FK_CANDIDATES_POST_1_1" FOREIGN
KEY("CANDIDATEID")
REFERENCES "HRMTEST"."JOB_CANDIDATES"("CANDIDATE_ID"),
CONSTRAINT "FK_DEPT_SEC_ID_1_1_1" FOREIGN KEY("DEPT_SEC_ID")
REFERENCES "HRMTEST"."DEPARTMENT_SECTION"("DEPT_SEC_ID"),
CONSTRAINT "FK_DESIG_ID_1_1_1" FOREIGN KEY("DESIG_ID")
REFERENCES "HRMTEST"."DESIGNATION"("DESIG_ID"),
CONSTRAINT "FK_INDUCTION_POST_1_1" FOREIGN
KEY("INDUCTION_ID")
REFERENCES "HRMTEST"."INDUCTION_TYPE"("INDUCTION_ID_PK"),
CONSTRAINT "FK_OPERATION_POST_1_1" FOREIGN
KEY("OPERATION_ID")
REFERENCES "HRMTEST"."OPERATIONTYPE"("OPERATION_ID_PK"),
CONSTRAINT "FK_SB_POST_OFFERED_1" FOREIGN
KEY("JOB_SB_CANDIDATE_ID")
REFERENCES
"HRMTEST"."JOB_SB_CANDIDATES"("JOB_SB_CANDIDATE_ID"),
CONSTRAINT "POST_OFFERED_ID_1_1" PRIMARY
KEY("POST_OFFERED_ID")



CREATE TABLE "HRMTEST"."JOB_QUALIFICATION" ("CANDIDATE_ID"
NUMBER(10) NOT NULL, "DEGREE" NUMBER(2) NOT NULL,
"INSTITUTENAME" VARCHAR2(100 byte) NOT NULL, "PASSINGYEAR"
VARCHAR2(4 byte), "SPECIALIZATION" VARCHAR2(50 byte), "GRADE"
VARCHAR2(10 byte),
CONSTRAINT "FK_CANDIDATE_QUAL_1" FOREIGN KEY("CANDIDATE_ID")
REFERENCES "HRMTEST"."JOB_CANDIDATES"("CANDIDATE_ID"),
CONSTRAINT "FK_DEGREE_QUAL_1" FOREIGN KEY("DEGREE")
REFERENCES "HRMTEST"."DEGREE"("DEGREE_ID"))




CREATE TABLE "HRMTEST"."JOB_SB_CANDIDATES"
("JOB_SB_CANDIDATE_ID" NUMBER(10) NOT NULL, "CANDIDATE_ID"
NUMBER(10) NOT NULL, "SB_MEETING_ID" NUMBER(10) NOT NULL,
"SELECTED_IN_SB" CHAR(1 byte), "SB_MERIT" NUMBER(10),
"SB_NO_OF_INC" NUMBER(11), "SB_REMARKS" VARCHAR2(100 byte),
"CANDIDATE_WAITING" VARCHAR2(10 byte),
CONSTRAINT "FK_SB_CANDIDATE_1_1" FOREIGN KEY("CANDIDATE_ID")
REFERENCES "HRMTEST"."JOB_CANDIDATES"("CANDIDATE_ID"),
CONSTRAINT "FK_SB_MEETING_ID_1_1" FOREIGN
KEY("SB_MEETING_ID")
REFERENCES "HRMTEST"."JOB_SB_MEETING"("SB_MEETING_ID"),
CONSTRAINT "PK_SB_CANDIDATE_ID_1_1" PRIMARY
KEY("JOB_SB_CANDIDATE_ID")



CREATE TABLE "HRMTEST"."JOB_SB_MEETING" ("SB_MEETING_ID"
NUMBER(10) NOT NULL, "ADVERTISE_ID" NUMBER(10) NOT NULL,
"SB_MEETING_NO" VARCHAR2(10 byte) NOT NULL, "SB_MEETING_DATE"
DATE NOT NULL,
CONSTRAINT "FK_SB_ADVERTISE_ID_1" FOREIGN KEY("ADVERTISE_ID")
REFERENCES "HRMTEST"."JOB_ADVERTISE"("ADVERTISE_ID"),
CONSTRAINT "PK_SB_MEETING_ID_1" PRIMARY KEY("SB_MEETING_ID")




CREATE TABLE "HRMTEST"."JOB_SD_CANDIDATES"
("JOB_SD_CANDIDATE_ID" NUMBER(10) NOT NULL,
"JOB_SB_CANDIDATE_ID" NUMBER(10) NOT NULL, "SD_MEETING_ID"
NUMBER(10) NOT NULL, "SELECTED_IN_SD" CHAR(1 byte),
"SD_MERIT" NUMBER(10), "SD_NO_OF_INC" NUMBER(11),
"SD_REMARKS" VARCHAR2(100 byte),
CONSTRAINT "FK_SD_SB_CANDIDATE_ID_1" FOREIGN
KEY("JOB_SB_CANDIDATE_ID")
REFERENCES
"HRMTEST"."JOB_SB_CANDIDATES"("JOB_SB_CANDIDATE_ID"),
CONSTRAINT "PK_SD_CANDIDATE_ID_1" PRIMARY
KEY("JOB_SD_CANDIDATE_ID")



CREATE TABLE "HRMTEST"."JOB_SD_MEETING" ("SD_MEETING_ID"
NUMBER(10) NOT NULL, "SD_MEETING_NO" VARCHAR2(10 byte) NOT
NULL, "SD_MEETING_DATE" DATE NOT NULL,
CONSTRAINT "PK_SD_MEETING_ID_1" PRIMARY KEY("SD_MEETING_ID")
















Confused
Re: Different Types of reports [message #287870 is a reply to message #287869] Thu, 13 December 2007 21:48 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
My SWAG is about 64 factorial or a very, very large number of possible reports; but some/many/most may be meaningless or useless.
Re: Different Types of reports [message #287871 is a reply to message #287869] Thu, 13 December 2007 22:10 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
I cannot understand what r u telling ?
Simple tell me that from these table how can i generate multiple reports.
recently i created these reports
Advertise Detail report
Duplicate Candidate Report
Candidate selected in Selection board
Candidate selected in Syndicate.
Re: Different Types of reports [message #287872 is a reply to message #287869] Thu, 13 December 2007 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
What information do the business users tell you they need in reports?

Re: Different Types of reports [message #287873 is a reply to message #287869] Thu, 13 December 2007 22:29 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
i have to create reports myself no one is a user yet so please tell about some different reports
Re: Different Types of reports [message #287895 is a reply to message #287873] Fri, 14 December 2007 00:24 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regarding scripts you used (sorry, showed us), I'm surprised you managed to write a single report. It is such a mess ... can't you put some effort in it and try to make it nice and clean (read: formatted -> at least, one line per table column)?

But OK, here you are:
- how many SD_MEETINGs were there this year?
- how many of them happened in July?
- how many of them will take place in the future?
- how many meetings were there per year?
- how many meetings were there per month per year?
etc.

Seriously: how are we supposed to know what is that bunch of tables for? There are no comments of any kind - you didn't describe tables nor columns. I'm afraid you'll have to make up your own reports (or would you like me to continue with my list)?
Re: Different Types of reports [message #287909 is a reply to message #287869] Fri, 14 December 2007 00:54 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
Thanks for your reply


I have not created one single report by all these tables
but i will create muliple reports by these tables

Can u tell me about other reports using other tables like job_candidates etcs

u tell me only about job_sd_meeting table
Re: Different Types of reports [message #287914 is a reply to message #287869] Fri, 14 December 2007 01:00 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>recently i created these reports
>Advertise Detail report
>Duplicate Candidate Report
>Candidate selected in Selection board
>Candidate selected in Syndicate.
Which statement above or below is true & valid???????????????
>I have not created one single report by all these tables
Are you expecting a single report which uses ALL this massive jumbled mass of random collection of tables?
Re: Different Types of reports [message #287917 is a reply to message #287869] Fri, 14 December 2007 01:05 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
all tables schema atteached
Re: Different Types of reports [message #287922 is a reply to message #287917] Fri, 14 December 2007 01:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm sorry but a can't download .doc file (the firewall blocks it). For the others, here's the formatted (and corrected) table creation script:
CREATE TABLE hrmTest.Job_Advertise (
  Advertise_Id    NUMBER(10) NOT NULL,
  Advertise_No    VARCHAR2(60 BYTE),
  Advertise_Date  DATE NOT NULL,
  desig_Id        VARCHAR2(5 BYTE) NOT NULL,
  No_Of_vaCancies NUMBER(10),
  dept_sec_Id     VARCHAR2(10 BYTE),
  PayScale_Id_pk  VARCHAR2(2 BYTE),
  cLosing_Date    DATE,
  CONSTRAINT fk_dept_sec_Id_1 
      FOREIGN KEY( dept_sec_Id )
      REFERENCES hrmTest.Department_Section( dept_sec_Id ),
  CONSTRAINT fk_desig_Id_1 
      FOREIGN KEY( desig_Id ) 
      REFERENCES hrmTest.Designation( desig_Id ),
  CONSTRAINT pk_Advertise_Id_1 
      PRIMARY KEY( Advertise_Id ),
  CONSTRAINT uk_Advertise_1 
      UNIQUE( Advertise_Date , desig_Id , dept_sec_Id ))
/

CREATE TABLE hrmTest.Job_Candidates (
  Candidate_Id                  NUMBER(10) NOT NULL,
  NAME                          VARCHAR2(40 BYTE) NOT NULL,
  Father_Name                   VARCHAR2(40 BYTE),
  Advertise_Id                  NUMBER(10) NOT NULL,
  eMail                         VARCHAR2(50 BYTE),
  Contact_No                    VARCHAR2(10 BYTE) NOT NULL,
  Postal_Address                VARCHAR2(100 BYTE),
  Dob                           DATE,
  nic                           VARCHAR2(15 BYTE),
  Accepted_In_Scrutiny          CHAR(1 BYTE),
  Test_Date                     DATE,
  Test_Status                   VARCHAR2(10 BYTE),
  Test_Remarks                  VARCHAR2(100 BYTE),
  Candidate_No                  VARCHAR2(20 BYTE),
  Scrutiny_Remarks              VARCHAR2(100 BYTE),
  Title                         VARCHAR2(10 BYTE),
  Gender                        CHAR(1 BYTE),
  Test_Letter_Issued            CHAR(1 BYTE),
  Test_Letter_Issue_Date        DATE,
  Interview_Letter_Issued       CHAR(1 BYTE),
  Interview_Letter_Issue_Date   DATE,
  Appointment_Letter_Issued     CHAR(1 BYTE),
  Appointment_Letter_Issue_Date DATE,
  CONSTRAINT fk_Advertise_Id_1 
      FOREIGN KEY( Advertise_Id ) 
      REFERENCES hrmTest.Job_Advertise( Advertise_Id ),
  CONSTRAINT pk_Candidate_Id_1 
      PRIMARY KEY( Candidate_Id ))
/

CREATE TABLE hrmTest.Job_Experience (
  Candidate_Id NUMBER(10) NOT NULL,
  OrganIsAtIon VARCHAR2(100 BYTE) NOT NULL,
  Post         VARCHAR2(50 BYTE),
  Grade        VARCHAR2(8 BYTE),
  DateFrom     DATE,
  DateTo       DATE,
  Description  VARCHAR2(200 BYTE),
  CONSTRAINT fk_Candidate_exp_1 
      FOREIGN KEY( Candidate_Id ) 
      REFERENCES hrmTest.Job_Candidates( Candidate_Id ))
/

CREATE TABLE hrmTest.Job_Letters (
  Letter_Id    NUMBER(10) NOT NULL,
  Letter_No    VARCHAR2(100 BYTE) NOT NULL,
  Letter_Date  DATE NOT NULL,
  Candidate_Id NUMBER(10) NOT NULL,
  Subject      VARCHAR2(700 BYTE) NOT NULL,
  Letter_Type  VARCHAR2(30 BYTE) NOT NULL)
/

CREATE TABLE hrmTest.Job_Post_Offered (
  Post_Offered_Id     NUMBER(10) NOT NULL,
  desig_Id            VARCHAR2(5 BYTE) NOT NULL,
  dept_sec_Id         VARCHAR2(3 BYTE),
  PayScale_Id_pk      VARCHAR2(2 BYTE),
  Induction_Id        VARCHAR2(2 BYTE) NOT NULL,
  Operation_Id        VARCHAR2(2 BYTE) NOT NULL,
  CandidateId         NUMBER(10) NOT NULL,
  Job_sb_Candidate_Id NUMBER(10) NOT NULL,
  CONSTRAINT fk_Candidates_Post_1_1 
      FOREIGN KEY( CandidateId ) 
      REFERENCES hrmTest.Job_Candidates( Candidate_Id ),
  CONSTRAINT fk_dept_sec_Id_1_1_1 
      FOREIGN KEY( dept_sec_Id ) 
      REFERENCES hrmTest.Department_Section( dept_sec_Id ),
  CONSTRAINT fk_desig_Id_1_1_1 
      FOREIGN KEY( desig_Id ) 
      REFERENCES hrmTest.Designation( desig_Id ),
  CONSTRAINT fk_Induction_Post_1_1 
      FOREIGN KEY( Induction_Id ) 
      REFERENCES hrmTest.Induction_Type( Induction_Id_pk ),
  CONSTRAINT fk_Operation_Post_1_1 
      FOREIGN KEY( Operation_Id ) 
      REFERENCES hrmTest.OperationType( Operation_Id_pk ),
  CONSTRAINT fk_sb_Post_Offered_1 
      FOREIGN KEY( Job_sb_Candidate_Id ) 
      REFERENCES hrmTest.Job_sb_Candidates( Job_sb_Candidate_Id ),
  CONSTRAINT Post_Offered_Id_1_1 
      PRIMARY KEY( Post_Offered_Id ))
/

CREATE TABLE hrmTest.Job_Qualification (
  Candidate_Id   NUMBER(10) NOT NULL,
  Degree         NUMBER(2) NOT NULL,
  InstituteName  VARCHAR2(100 BYTE) NOT NULL,
  PassingYear    VARCHAR2(4 BYTE),
  Specialization VARCHAR2(50 BYTE),
  Grade          VARCHAR2(10 BYTE),
  CONSTRAINT fk_Candidate_qual_1 
      FOREIGN KEY( Candidate_Id ) 
      REFERENCES hrmTest.Job_Candidates( Candidate_Id ),
  CONSTRAINT fk_Degree_qual_1 
      FOREIGN KEY( Degree ) 
      REFERENCES hrmTest.Degree( Degree_Id ))
/


CREATE TABLE hrmTest.Job_sb_Candidates (
  Job_sb_Candidate_Id NUMBER(10) NOT NULL,
  Candidate_Id        NUMBER(10) NOT NULL,
  sb_Meeting_Id       NUMBER(10) NOT NULL,
  Selected_In_sb      CHAR(1 BYTE),
  sb_Merit            NUMBER(10),
  sb_No_Of_Inc        NUMBER(11),
  sb_Remarks          VARCHAR2(100 BYTE),
  Candidate_WaitIng   VARCHAR2(10 BYTE),
  CONSTRAINT fk_sb_Candidate_1_1 
      FOREIGN KEY( Candidate_Id ) 
      REFERENCES hrmTest.Job_Candidates( Candidate_Id ),
  CONSTRAINT fk_sb_Meeting_Id_1_1 
      FOREIGN KEY( sb_Meeting_Id ) 
      REFERENCES hrmTest.Job_sb_Meeting( sb_Meeting_Id ),
  CONSTRAINT pk_sb_Candidate_Id_1_1 
      PRIMARY KEY( Job_sb_Candidate_Id ))
/

CREATE TABLE hrmTest.Job_sb_Meeting (
  sb_Meeting_Id   NUMBER(10) NOT NULL,
  Advertise_Id    NUMBER(10) NOT NULL,
  sb_Meeting_No   VARCHAR2(10 BYTE) NOT NULL,
  sb_Meeting_Date DATE NOT NULL,
  CONSTRAINT fk_sb_Advertise_Id_1 
      FOREIGN KEY( Advertise_Id ) 
      REFERENCES hrmTest.Job_Advertise( Advertise_Id ),
  CONSTRAINT pk_sb_Meeting_Id_1 
      PRIMARY KEY( sb_Meeting_Id ))
/


CREATE TABLE hrmTest.Job_sd_Candidates (
  Job_sd_Candidate_Id NUMBER(10) NOT NULL,
  Job_sb_Candidate_Id NUMBER(10) NOT NULL,
  sd_Meeting_Id       NUMBER(10) NOT NULL,
  Selected_In_sd      CHAR(1 BYTE),
  sd_Merit            NUMBER(10),
  sd_No_Of_Inc        NUMBER(11),
  sd_Remarks          VARCHAR2(100 BYTE),
  CONSTRAINT fk_sd_sb_Candidate_Id_1 
      FOREIGN KEY( Job_sb_Candidate_Id ) 
      REFERENCES hrmTest.Job_sb_Candidates( Job_sb_Candidate_Id ),
  CONSTRAINT pk_sd_Candidate_Id_1 
      PRIMARY KEY( Job_sd_Candidate_Id ))
/

CREATE TABLE hrmTest.Job_sd_Meeting (
  sd_Meeting_Id   NUMBER(10) NOT NULL,
  sd_Meeting_No   VARCHAR2(10 BYTE) NOT NULL,
  sd_Meeting_Date DATE NOT NULL,
  CONSTRAINT pk_sd_Meeting_Id_1 
      PRIMARY KEY( sd_Meeting_Id ))
/


MHE
Re: Different Types of reports [message #288109 is a reply to message #287869] Fri, 14 December 2007 21:33 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
Thanks for correction of scripts

I am again telling that by using these tables
Name some reports that i can create .


Re: Different Types of reports [message #288111 is a reply to message #287869] Fri, 14 December 2007 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Maaher,
THANKS for the formatting. At least they can now be read & understood.

Only two tables, hrmTest.Job_sd_Meeting & hrmTest.Job_Letters do not have foreign key constraints.
So I wonder how some/many/most/all other tables can actually be INSERTed with data;
due to proverbial chicken & egg situation WRT the PK & FK constraints.
I suspect these tables were "designed" in a vacuum without any consideration about having to actually get records into them.

ambreensh,
One of the posting guidelines is that you should provide sample test data by posting INSERT statement to facilitate testing.
Such data is typically used during unit testing.
Re: Different Types of reports [message #288121 is a reply to message #287869] Fri, 14 December 2007 23:06 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
Please post the query of
How many SD_meeting were this year?
or other these types of qurey
Re: Different Types of reports [message #288122 is a reply to message #287869] Fri, 14 December 2007 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>How many SD_meeting were this year?
SELECT COUNT(pk_sd_Meeting_Id_1) FROM hrmTest.Job_sd_Meeting WHERE sd_Meeting_Date >= TO_DATE('2007-01-01','YYYY-MM-DD');

Now it is your turn.
Post YOUR code for the previously generated reports.
>recently i created these reports
>Advertise Detail report
>Duplicate Candidate Report
>Candidate selected in Selection board
>Candidate selected in Syndicate.
Re: Different Types of reports [message #288123 is a reply to message #287869] Fri, 14 December 2007 23:38 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
I give you queries of two reports

Advertise detail report


select distinct
de.VCHARDESIGNAME,
nvl(ds.dept_sec_id,0) deptsecid ,
jd.advertise_no,jd.advertise_date,
to_char(jd.no_of_vacancies) as vacancy,
temp.cnt_candidates
,to_char(interview.cnt_passed) as passed
from
job_advertise jd , department_section ds
,department dp,designation de,job_candidates jc,
(select advertise_id,to_char(count(candidate_id)) cnt_candidates from
job_candidates group by advertise_id ) temp,
(select advertise_id ,to_char(count(candidate_id)) cnt_passed from job_candidates
where test_status='P' group by advertise_id ) interview
where
jd.advertise_id = temp.advertise_id
and
jd.advertise_id=interview.advertise_id(+)
and jd.desig_id=de.desig_id
and ds.dept_sec_id(+) =jd.dept_sec_id
and jc.advertise_id=jd.advertise_id
order by jd.advertise_date


Duplicate Candidate Report


select jc.candidate_id,candidate_no,name,father_name,jc.nic
,ad.ADVERTISE_DATE ,de.VCHARDESIGNAME,
dept.VCHARDEPT_NAME,test_status,
ad.payscale_id_pk, ACCEPTED_IN_SCRUTINY ,
sb.SELECTED_IN_SB
,sd.selected_in_sd
from
job_sd_candidates sd,
job_candidates jc, job_advertise ad,department dept ,
department_section ds, job_sb_candidates sb ,
designation de,
(select count(*),jc.nic,ja.desig_id from job_candidates jc
,job_advertise ja where ja.advertise_id=jc.advertise_id
group by nic, desig_id having count(*) >1 ) a
where
jc.nic = a.nic
and dept.department_id_pk=ds.department_id_pk
and ds.dept_sec_id=ad.dept_sec_id
and ad.advertise_id=jc.advertise_id
and sb.candidate_id(+)=jc.candidate_id
and sb.job_sb_candidate_id=sd.job_sb_candidate_id(+)
and ad.desig_id=de.desig_id
order by name

Give the quries for these reports ??
how many of them happened in July?
- how many of them will take place in the future?
- how many meetings were there per year?
- how many meetings were there per month per year?

Can you tell me that if i call these reports from form than what paramater should i use to call a reports

and also
give some other reports from other tables
like job_sb_meeting,job_candidates , job_sb_meeting, job_advertise etc ( as mention earlier)


Thanks












Re: Different Types of reports [message #288125 is a reply to message #287869] Fri, 14 December 2007 23:51 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
I want to see the other 2 reports also
>Candidate selected in Selection board
>Candidate selected in Syndicate.
Re: Different Types of reports [message #288126 is a reply to message #287869] Fri, 14 December 2007 23:54 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
i need these reports as soon as possible .

my email address ambreenshamshad@yahoo.com
Re: Different Types of reports [message #288127 is a reply to message #287869] Fri, 14 December 2007 23:57 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
selection_board report

select distinct ja.advertise_date ,ja.advertise_no,jc.father_name,jc.contact_no,jpo.payscale_id_pk,
jc.postal_address,
de.VCHARDESIGNAME,
dep.VCHARDEPT_NAME,jsm.sb_meeting_date,
jsm.sb_meeting_no,jc.candidate_no,jc.title||' '||jc.name as name ,jsb.selected_in_sb
from job_candidates jc,
job_sb_candidates jsb ,
job_sb_meeting jsm, job_post_offered jpo,
department dep ,
department_section ds,
designation de,
job_advertise ja
where jsb.sb_meeting_id=jsm.sb_meeting_id
and jsm.advertise_id=ja.advertise_id
and jc.candidate_id=jsb.candidate_id
and jsb.candidate_id =jpo.candidateid
and jsm.sb_meeting_no=:meeting_no
and jsm.sb_meeting_date=:meeting_date
and dep.DEPARTMENT_ID_PK=ds.DEPARTMENT_ID_PK
and de.desig_id=jpo.desig_id
and jpo.dept_sec_id=ds.dept_sec_id
--and jpo.job_sb_candidate_id =jsb.job_sb_candidate_id
and jsb.selected_in_sb='A'
order by to_number(jc.candidate_no)



syndicate report

select distinct ja.advertise_date ,jc.father_name,
jc.contact_no,jpo.payscale_id_pk,
jc.postal_address,
de.VCHARDESIGNAME,
dep.VCHARDEPT_NAME,jsm.sd_meeting_date,
jsm.sd_meeting_no,jc.candidate_no,
jc.title||' '||jc.name as name ,jsd.selected_in_sd
from job_candidates jc,
job_sb_candidates jsb ,
job_sd_meeting jsm,
job_sd_candidates jsd,
department dep ,
department_section ds,
designation de,
job_advertise ja,
job_post_offered jpo
where
jsd.job_sb_candidate_id=jsb.job_sb_candidate_id
and jsd.sd_meeting_id=jsm.sd_meeting_id
and jc.advertise_id=ja.advertise_id
and jsb.candidate_id=jc.candidate_id
and jpo.candidateid=jsb.candidate_id
and jsm.sd_meeting_no=:meeting_no
and jsm.sd_meeting_date=:meeting_date
and dep.DEPARTMENT_ID_PK=ds.DEPARTMENT_ID_PK
and ds.dept_sec_id=jpo.dept_sec_id
and de.desig_id=jpo.desig_id
and jsd.selected_in_sd='A'
--and jpo.post_offered_id=jsd.post_offered_id
order by to_number(jc.candidate_no)




Why u need all these reports ????????????
Re: Different Types of reports [message #288128 is a reply to message #287869] Sat, 15 December 2007 00:00 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
i need reports just like littlefoot post
Here is the message
Regarding scripts you used (sorry, showed us), I'm surprised you managed to write a single report. It is such a mess ... can't you put some effort in it and try to make it nice and clean (read: formatted -> at least, one line per table column)?

But OK, here you are:
- how many SD_MEETINGs were there this year?
- how many of them happened in July?
- how many of them will take place in the future?
- how many meetings were there per year?
- how many meetings were there per month per year?
etc.

Seriously: how are we supposed to know what is that bunch of tables for? There are no comments of any kind - you didn't describe tables nor columns. I'm afraid you'll have to make up your own reports (or would you like me to continue with my list)?
Re: Different Types of reports [message #288129 is a reply to message #287869] Sat, 15 December 2007 00:02 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
I want to know if you did these 4 reports why do you need assistance to:
>Give the quries for these reports ??
>how many of them happened in July?
>- how many of them will take place in the future?
>- how many meetings were there per year?
>- how many meetings were there per month per year?

They are no different than the other 4 reports.
Re: Different Types of reports [message #288133 is a reply to message #287869] Sat, 15 December 2007 00:22 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
i can create these reports but i don't find that how many colums should i used
and can i place all these criteria in single report

can i use the form and pass parmeter in reports

if i generate the report for

How many sd_meetings were there this year ? then this only return one column .

so i should know what is the best interface to use these reports .

and also i little bit confuse in date fuction .

i need others reports with best interface so that my module look better
and if some one use this module , he get much more information
I need some others reports by using these table

any one can help me as soon as possible !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Re: Different Types of reports [message #288137 is a reply to message #288133] Sat, 15 December 2007 00:33 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

YOu must read "introduction to sql" book.
Learn how to write query ?
what is possible way to design report ?

If this time somebody write all queries for you then think who help you next time.

Sorry, may be my comments bother you. but it is true which you face in your professional life.

go through below link
http://tahiti.oracle.com
instead of this I recommended use "student guide" book which come with oracle course material.

All the best

[Updated on: Sat, 15 December 2007 00:34]

Report message to a moderator

Re: Different Types of reports [message #288138 is a reply to message #287869] Sat, 15 December 2007 00:48 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
if u don't like to help me then you do but

i know how to write quries but

i only need that name the business reports which i generate

using these table


Re: Different Types of reports [message #288139 is a reply to message #287869] Sat, 15 December 2007 01:00 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>i know how to write quries
I am NOT convinced you can even spell SQL
>i only need that name the business reports which i generate
using these table
Simply concatenate the table_name together for report_names.

Good Luck, because I have no idea how you found yourself in the sad position where you find yourself now.
Re: Different Types of reports [message #288142 is a reply to message #287869] Sat, 15 December 2007 01:12 Go to previous messageGo to next message
ambreensh
Messages: 20
Registered: December 2007
Location: Karachi
Junior Member
no body like to help me oK

I not need the name of reports but some reports whisch i generate by these table having multiple field as shown earlier


thanks
Re: Different Types of reports [message #288143 is a reply to message #287869] Sat, 15 December 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey, you missed something in her profile: "Occupation: Manager"

Regards
Michel
Re: Different Types of reports [message #288198 is a reply to message #287869] Sat, 15 December 2007 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member

Frank, hobbes, MarcL, joy_division, Mahesh Rajendran, Barbara Boehmer, rleishman, rajavu1

which are you are going hand hold this Newbie?
Re: Different Types of reports [message #288202 is a reply to message #287869] Sat, 15 December 2007 16:41 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Ambreen,

As a new member of this forum, perhaps you have not yet noticed the forum guide that was created to help guide new members. You can find it at the top of the forum page or by clicking on the link below:

http://www.orafaq.com/forum/t/88153/0/

Please review it and follow it. This forum is not intended to be a place where you get others to do your work for you. Nobody gets paid to do anything here. We are all volunteers. It is intended to help you with specific problems that you cannot solve yourself even after searching and experimenting on your own.

It appears that you are trying to create a sampling of various reports based on some tables for some unknown purpose, without having any idea who might want those reports for what purpose. This is backwards from the way things normally are done. Typically, there is an identified need for some type of data to be stored and retrieved in a certain fashion, then a database is designed and reports are designed based on those needs.

If you expect the needed reports to be of a standard type, then you can create various ones using something like Oracle's reports module or Crystal Reports. If you expect that the types of reports needed may be so wide and varied that you cannot predict them in advance, then you may want to look into something like Oracle's Discoverer product, which allows users to create ad-hoc reports. You may want to use a combination of things, with one type for one purpose and another type for another purpose.

If you are a manager and you are this lost and unable to even state your problems clearly or know what to use, then you should hire an Oracle consultant.

Good luck,

Barbara



Previous Topic: SQL questions from psycroptik (merged)
Next Topic: Update Query
Goto Forum:
  


Current Time: Sat Dec 03 17:47:09 CST 2016

Total time taken to generate the page: 0.14860 seconds