Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Return one row using UNION

    Hi, I've created a union query to try and return one row of data:

    Code:
    SELECT PERSON.ID_NUMBER, PERSON.TITLE, PERSON.FIRST_NAME,
             PERSON.SURNAME, TO_CHAR(NULL) AS PHONE, TO_CHAR(NULL) AS CONTACT,
             TO_CHAR(NULL) AS MOBILE_PHONE, ADDRESS.ADDRESS_LINE1 AS HALL,
             ADDRESS.ADDRESS_LINE2 AS ROOM, COURSE.DESCRIPTION AS COURSE,
             COURSE_INSTANCE.COURSE_YEAR AS YEAR, COURSE_INSTANCE.ACADEMIC_YEAR,
             COURSE.COURSE AS CODE
    FROM PERSON, ADDRESS, STUDENT_COURSE_DETAIL, COURSE_INSTANCE, COURSE,
             STATUS, STUDENT_CATEGORY
    WHERE PERSON.TERM_ADDRESS = ADDRESS.OBJECT_ID AND
             PERSON.OBJECT_ID = STUDENT_COURSE_DETAIL.PERSON AND
             STUDENT_COURSE_DETAIL.COURSE_INSTANCE = COURSE_INSTANCE.OBJECT_ID AND
             COURSE_INSTANCE.COURSE = COURSE.OBJECT_ID AND
             STUDENT_COURSE_DETAIL.STATUS = STATUS.OBJECT_ID AND
             STUDENT_COURSE_DETAIL.STUDENT_CATEGORY = STUDENT_CATEGORY.OBJECT_ID AND
             (STUDENT_CATEGORY.STUDENT_CATEGORY LIKE 'RES') AND (STATUS.STATUS = 'R') AND
             (NOT (COURSE.DESCRIPTION LIKE '*CCC*')) AND
             (COURSE_INSTANCE.ACADEMIC_YEAR = (CASE WHEN TO_CHAR(SYSDATE, 'MM') NOT IN ('09', '10', '11', '12') THEN TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1 ELSE TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) END))
    UNION
    SELECT PERSON.ID_NUMBER, PERSON.TITLE, PERSON.FIRST_NAME,
             PERSON.SURNAME, ADDRESS.PHONE, ADDRESS.CONTACT, ADDRESS.MOBILE_PHONE,
             TO_CHAR(NULL) AS HALL, TO_CHAR(NULL) AS ROOM,
             COURSE.DESCRIPTION AS COURSE, COURSE_INSTANCE.COURSE_YEAR AS YEAR,
             COURSE_INSTANCE.ACADEMIC_YEAR, COURSE.COURSE AS CODE
    FROM PERSON, ADDRESS, STUDENT_COURSE_DETAIL, COURSE_INSTANCE, COURSE,
            STATUS, STUDENT_CATEGORY
    WHERE PERSON.ADDRESS = ADDRESS.OBJECT_ID AND
            PERSON.OBJECT_ID = STUDENT_COURSE_DETAIL.PERSON AND
            STUDENT_COURSE_DETAIL.COURSE_INSTANCE = COURSE_INSTANCE.OBJECT_ID AND
            COURSE_INSTANCE.COURSE = COURSE.OBJECT_ID AND
            STUDENT_COURSE_DETAIL.STATUS = STATUS.OBJECT_ID AND
            STUDENT_COURSE_DETAIL.STUDENT_CATEGORY = STUDENT_CATEGORY.OBJECT_ID AND
            (STUDENT_CATEGORY.STUDENT_CATEGORY LIKE 'RES') AND (STATUS.STATUS = 'R') AND
            (NOT (COURSE.DESCRIPTION LIKE '*CCC*')) AND
            (COURSE_INSTANCE.ACADEMIC_YEAR = (CASE WHEN TO_CHAR(SYSDATE, 'MM') NOT IN ('09', '10', '11', '12') THEN TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1 ELSE TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) END))
    ORDER BY SURNAME
    When I run this query I get two rows I think this is because in one query I join to the TERM_ADDRESS table to get some information, and the other joins the ADDRESS table to get the rest.

    How could I combine the two to produce just one row of information?
    <- Hides behind a rock.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by KevCB226
    When I run this query I get two rows I think this is because in one query I join to the TERM_ADDRESS table to get some information, and the other joins the ADDRESS table to get the rest.
    Depending on the existence of the corresponding row(s) for TERM_ADDRESS and ADDRESS columns in ADDRESS table (if OBJECT_ID is unique there), you may get 0-2 rows depending on content of that columns.
    How could I combine the two to produce just one row of information?
    JOIN ADDRESS table twice in one query. Use table alias for at least one occurence of that table. You may use OUTER JOIN to display the result even when no address is filled in TERM_ADDRESS or ADDRESS column.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks,

    I've added the Address table twice, added an alias, and included the additional fields needed from the second address table.

    When I run the query it returns the following:

    Error at Command Line:6 Column:30
    Error report:
    SQL Error: ORA-00933: SQL command not properly ended
    00933. 00000 - "SQL command not properly ended"
    *Cause:
    *Action:
    Code:
    SELECT PERSON.ID_NUMBER, PERSON.TITLE, PERSON.FIRST_NAME,
             PERSON.SURNAME, ADDRESS.PHONE, ADDRESS.CONTACT, ADDRESS.MOBILE_PHONE,
             TERM.ADDRESS_LINE1 AS HALL, TERM.ADDRESS_LINE2 AS ROOM,
             COURSE.DESCRIPTION AS COURSE, COURSE_INSTANCE.COURSE_YEAR AS YEAR,
             COURSE_INSTANCE.ACADEMIC_YEAR, COURSE.COURSE AS CODE
    FROM PERSON, ADDRESS, ADDRESS AS TERM, STUDENT_COURSE_DETAIL, COURSE_INSTANCE, COURSE,
            STATUS, STUDENT_CATEGORY
    WHERE PERSON.ADDRESS = ADDRESS.OBJECT_ID AND
            PERSON.TERM_ADDRESS = TERM.OBJECT_ID AND
            PERSON.OBJECT_ID = STUDENT_COURSE_DETAIL.PERSON AND
            STUDENT_COURSE_DETAIL.COURSE_INSTANCE = COURSE_INSTANCE.OBJECT_ID AND
            COURSE_INSTANCE.COURSE = COURSE.OBJECT_ID AND
            STUDENT_COURSE_DETAIL.STATUS = STATUS.OBJECT_ID AND
            STUDENT_COURSE_DETAIL.STUDENT_CATEGORY = STUDENT_CATEGORY.OBJECT_ID AND
            (STUDENT_CATEGORY.STUDENT_CATEGORY LIKE 'RES') AND (STATUS.STATUS = 'R') AND
            (NOT (COURSE.DESCRIPTION LIKE '*CCC*')) AND
            (COURSE_INSTANCE.ACADEMIC_YEAR = (CASE WHEN TO_CHAR(SYSDATE, 'MM') NOT IN ('09', '10', '11', '12') THEN TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1 ELSE TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) END))
    ORDER BY SURNAME
    <- Hides behind a rock.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    And your Oracle version (four decimals) is?
    Anyway, at least in 10g, Oracle does not support the optional AS keyword before a table alias, as documented in SQL Reference:
    SELECT Statement
    Oracle Compliance To Core SQL:2003 (have a look at E051-08)

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Sorry, it is 10g I'm using.

    Managed to sort it out, thanks for the links.
    <- Hides behind a rock.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •