1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

problems with report using isql*plus

Discussion in 'SQL PL/SQL' started by stroodle, Nov 18, 2008.

  1. stroodle

    stroodle Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi there,

    As the title of this topic illustrates, i'm having trouble dumping relevant data from columns of different tables. I have three tables appropriatly related. A 'course' table, 'student' and 'next_of_kin' tables. I have many students enrolled on various courses but only a hanfull of courses offer the module option 'Database Systems'. I have no 'module' table but i know the three course names which provide the module option. I intend on producing a report hich lists all students enrolled on the courses which provid the module option 'Database Systems'. I have attempted the report but i keep getting a 'cartesian product' displaying all next_of_kin names instead of the appropriate. Also i am struggling to come up with the right WHERE statement to depict only the three courses which provide the module option 'Database Systems' as defined by 'courseNo' in both 'course' and 'student' tables.

    Here is the most recent attempt: :eek:

    Code (Text):
    [B]--set echo off
    --set pagesize 24
    --set feedback off
    --set linesize 78

    col A format 99999999 heading 'Student No'
    col B format A15 heading 'Student Name'
    col C format A15 heading 'Course Name'
    col D format 99999999 'Course No'
    col E format A10 heading 'Next-of-Kin'
    break on A skip 1 on B

    TTitle 'Business Studies 1 Option BT300'
    BTitle 'Prepared By : Richard Stroud / 20527796'

    Select student.StudentNo "Student No",
    student.fName || ' ' || student.lName "Student Name",
    course.title "Course Name",
    course.courseNo "Course No",
    next_of_kin.fName || ' ' || next_of_kin.lName "Next-of-Kin"

    From student, course, next_of_kin
    where course.courseNo = '12885545'

    order by studentNo

    --clear columns
    --TTitle off
    --BTitle off
    --set feedback on
    --set pagesize 24
    --clear breaks[/B]
    Any help towards this toipic will be GREATLY appreciated :)
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It looks like a homework :)

    Anyway , we cannot help this unless you provide the description of all three tables and insert script to produce the test data. Because you know the table structure better than us at this moment. Also explain the actual requirement.

    As you mentioned the reason for the Cartesian product is improper where clause. We need to know the actual fields and data to avoid the Cartesian product.
     
  3. stroodle

    stroodle Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi there!

    Thanks for your interest in my thread.

    As i mentioned in the thread, i have three tables. The 'student' table comprises of the following:

    Code (Text):

    Name Null? Type

    STUDENTNO NOT NULL NUMBER(8)
    FNAME VARCHAR2(10)
    LNAME VARCHAR2(10)
    STREET VARCHAR2(25)
    CITY VARCHAR2(25)
    POSTCODE VARCHAR2(8)
    DOB DATE
    SEX CHAR(1)
    CATEGORY CHAR(2)
    NATIONALITY VARCHAR2(15)
    SMOKER CHAR(1)
    SPECIAL_NEEDS VARCHAR2(100)
    COMMENTS VARCHAR2(100)
    STATUS CHAR(1)
    COURSENO NUMBER(8)
    ADVISORNO NUMBER(8)
    NEXT_OF_KINNO NUMBER(8)
     
    The 'course' table of the following:

    Code (Text):

    Name Null? Type

    COURSENO NOT NULL NUMBER(8)
    TITLE VARCHAR2(30)
    YEAR NUMBER(4)
    COURSE_LEADER VARCHAR2(25)
    DEPARTMENT VARCHAR2(30)
     
    And finally the 'next_of_kin' table:

    Code (Text):

    Name Null? Type

    NOKNO NOT NULL NUMBER(8)
    FNAME VARCHAR2(10)
    LNAME VARCHAR2(10)
    RELATIONSHIP VARCHAR2(20)
    STREET VARCHAR2(25)
    CITY VARCHAR2(25)
    POSTCODE VARCHAR2(8)
    TELNO NUMBER(11)
     
    The PK's have been assigned as the first column of each table i.e 'studentNo', 'courseNo' and 'nokNo'.
    The course table is parent to the student, a student can only be enrolled on one course at a time. Also, the nokNo of next_of_kin is FK'd within the student table (next_of_kinNo). Will i need to create another 'studentNo' column within the 'next_of_kin' table?

    The requirement is to create a report table to produce a list of students who have chosen a particular module on a course. There are two courses (Business studies and computing) which offer the module and about 3 students altogether whom are enrolled on the courses.

    The table will need to display the student No from the student table alond with the students first + last names, the course name, the courses number and the next-of-kin first + last names (ordered by the student no).
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Try the following query . ( I didn't try it yet )

    Code (Text):
    Select s.StudentNo "Student No",
             s.fName || ' ' || s.lName "Student Name",
             c.title "Course Name",
             c.courseNo "Course No",
             ( SELECT n.fName || ' ' || n.lName
               FROM  next_of_kin n
               WHERE  n.nOkno=s.next_Of_kinno) "Next-of-Kin"
    From student s, course c
    where s.courseNo = c. courseNo
    course.courseNo = '12885545'
    order by studentNo
    It is called Correlated subquery. There are other some methods available for the same . I recommend you to find out and try those and also post here in this thread for the future reference.
     
  5. stroodle

    stroodle Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanx for your efforts. I have tested your correlated subquery but i get the error

    Code (Text):

    'SP2-0158: unknown COLUMN option "'Course"
    course.courseNo = '12885545' ' Which ive failed to understand.
    *
     
    I have tried the following:

    Code (Text):
    SELECT student.StudentNo "Student No",
    student.fName || ' ' || student.lName "Student Name",
    course.title "Course Name",
    course.courseNo "Course No",
    next_of_kin.fName || ' ' || next_of_kin.lName "Next-of-Kin"

    From student, course, next_of_kin
    where course.courseNo = student.studentNo
    and student.studentNo = next_of_kinNo
    and course.courseNo = '12885545'
    or course.courseNo = '12243445'

    ORDER BY studentNo
     
    I get the same cartesian product. Could it have something to do with my FK's? Its just that the next_of_kin table doenst have a list af all relative students (although next_of_kinNo is stored within the student table)
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Wasn't it a small mistake that you could have rectified ? Try the following .( I didn't try it even)

    Code (Text):
    Select s.StudentNo "Student No",
             s.fName || ' ' || s.lName "Student Name",
             c.title "Course Name",
             c.courseNo "Course No",
             ( SELECT n.fName || ' ' || n.lName
               FROM  next_of_kin n
               WHERE  n.nOkno=s.next_Of_kinno) "Next-of-Kin"
    From   student s, course c
    where s.courseNo = c. courseNo
    and    c.courseNo = '12885545'
    order by studentNo
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I just tried to rewrite your second query as below .

    Code (Text):
    SELECT student.StudentNo "Student No",
               student.fName || ' ' || student.lName "Student Name",
               course.title "Course Name",
               course.courseNo "Course No",
               next_of_kin.fName || ' ' || next_of_kin.lName "Next-of-Kin"
    From student, course, next_of_kin
    where course.courseNo = student.studentNo
    and student.next_Of_kinno = next_of_kin.nokno
    and (course.courseNo = '12885545' or course.courseNo = '12243445' )
    It will work if all the student are having next_Of_kinn .
     
  8. stroodle

    stroodle Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    It worked!! you are a guru my friend! Its just that i need to display two courses which offer the module. I have tried

    Code (Text):
    From   student s, course c
    where s.courseNo = c. courseNo
    and    c.courseNo = '12885545' or c.courseNo = '12243445'
    order by studentNo
     
    But again, i am fronted with the cortesian product. Will you be able to help with this?
     
  9. stroodle

    stroodle Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hey

    I figured it!

    Code (Text):
    and    (c.courseNo = '12885545' or c.courseNo = '12243445')
    Thanx for you input Raj!
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (Text):
    From student s, course c
    where s.courseNo = c. courseNo
    course.courseNo IN ( '12885545','12243445)
    order by studentNo
    Will work .