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

    Unanswered: Need help changing access query to view

    Hi folks,

    I'm having difficulty converting an access query into a sql server view.

    This is the structure of the query, which uses linked tables.

    http://img356.imageshack.us/my.php?i...ructureuu0.gif

    Access SQL:
    Code:
    SELECT DISTINCT QUERCUS_PERSON.FIRST_NAME, QUERCUS_PERSON.SURNAME, QUERCUS_COURSE.COURSE, QUERCUS_COURSE.DESCRIPTION, QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR
    FROM QUERCUS_PERSON_TYPE INNER JOIN ((QUERCUS_COURSE
    INNER JOIN ((QUERCUS_PERSON INNER JOIN QUERCUS_PERSON_DETAIL ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_PERSON_DETAIL.PERSON)
    INNER JOIN QUERCUS_COURSE_TUTOR ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_COURSE_TUTOR.PERSON) ON QUERCUS_COURSE.OBJECT_ID = QUERCUS_COURSE_TUTOR.COURSE)
    INNER JOIN QUERCUS_COURSE_INSTANCE ON QUERCUS_COURSE.OBJECT_ID = QUERCUS_COURSE_INSTANCE.COURSE) ON QUERCUS_PERSON_TYPE.OBJECT_ID = QUERCUS_PERSON_DETAIL.PERSON_TYPE
    WHERE (((QUERCUS_PERSON_TYPE.DESCRIPTION)="Tutor"))
    ORDER BY QUERCUS_PERSON.SURNAME;
    When I try and create a view based on this query I'm not getting the same results back.

    This is what I have tried in the view so far, where all tables used are from a linked server:

    View Attempt 1:
    Code:
    SELECT DISTINCT 
                          TOP (100) PERCENT qp.FIRST_NAME AS FirstName, qp.SURNAME, qc.COURSE, qc.DESCRIPTION AS CourseTitle, qci.ACADEMIC_YEAR AS AcYr
    FROM         QUERCUS..QUERCUS.PERSON AS qp INNER JOIN
                          QUERCUS..QUERCUS.PERSON_DETAIL AS qpd ON qp.OBJECT_ID = qpd.PERSON INNER JOIN
                          QUERCUS..QUERCUS.PERSON_TYPE AS qpt ON qpd.PERSON_TYPE = qpt.OBJECT_ID INNER JOIN
                          QUERCUS..QUERCUS.COURSE_TUTOR AS qct ON qp.OBJECT_ID = qct.PERSON INNER JOIN
                          QUERCUS..QUERCUS.COURSE AS qc ON qct.COURSE = qc.OBJECT_ID INNER JOIN
                          QUERCUS..QUERCUS.COURSE_INSTANCE AS qci ON qc.OBJECT_ID = qci.COURSE
    WHERE     (qpt.DESCRIPTION = 'Tutor')
    ORDER BY qp.SURNAME
    View Attempt 2:
    Code:
    SELECT DISTINCT 
                          TOP (100) PERCENT qp.FIRST_NAME AS FirstName, qp.SURNAME, qc.COURSE, qc.DESCRIPTION AS CourseTitle, qci.ACADEMIC_YEAR AS AcYr
    FROM         QUERCUS..QUERCUS.PERSON_TYPE AS qpt INNER JOIN
                          QUERCUS..QUERCUS.COURSE AS qc INNER JOIN
                          QUERCUS..QUERCUS.PERSON AS qp INNER JOIN
                          QUERCUS..QUERCUS.PERSON_DETAIL AS qpd ON qp.OBJECT_ID = qpd.PERSON INNER JOIN
                          QUERCUS..QUERCUS.COURSE_TUTOR AS qct ON qp.OBJECT_ID = qct.PERSON ON qc.OBJECT_ID = qct.COURSE INNER JOIN
                          QUERCUS..QUERCUS.COURSE_INSTANCE AS qci ON qc.OBJECT_ID = qci.COURSE ON qpt.OBJECT_ID = qpd.PERSON_TYPE
    WHERE     (qpt.DESCRIPTION = 'Tutor')
    ORDER BY qp.SURNAME
    Anyone help?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    whats the difference in the results? what version of SQL is this. I remember reading that in SQL 2005 the query processor was going to ignore SELECT TOP 100 PERCENT with a ORDER BY.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You don't put an ORDER BY within the view.
    Do it like this
    Code:
    CREATE VIEW dbo.my_view99
      AS
    SELECT DISTINCT
           QUERCUS_PERSON.FIRST_NAME
         , QUERCUS_PERSON.SURNAME
         , QUERCUS_COURSE.COURSE
         , QUERCUS_COURSE.DESCRIPTION
         , QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR
    FROM   QUERCUS_PERSON
     INNER
      JOIN QUERCUS_PERSON_DETAIL
        ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_PERSON_DETAIL.PERSON
     INNER
      JOIN QUERCUS_COURSE_TUTOR
        ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_COURSE_TUTOR.PERSON
     INNER
      JOIN QUERCUS_PERSON_TYPE
        ON QUERCUS_PERSON_DETAIL.PERSON_TYPE = QUERCUS_PERSON_TYPE.OBJECT_ID
     INNER
      JOIN QUERCUS_COURSE
        ON QUERCUS_COURSE.OBJECT_ID = QUERCUS_COURSE_TUTOR.COURSE
     INNER
      JOIN QUERCUS_COURSE_INSTANCE
        ON QUERCUS_COURSE.OBJECT_ID = QUERCUS_COURSE_INSTANCE.COURSE    
    WHERE QUERCUS_PERSON_TYPE.DESCRIPTION = 'Tutor'
    GO
    
    SELECT *
    FROM   dbo.my_view99
    ORDER
        BY SURNAME
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by Thrasymachus
    whats the difference in the results? what version of SQL is this.
    When running the query in access I get 1900 rows returned, in SQL Server I get 939 rows returned. I am using SQL Server 2005.

    Quote Originally Posted by georgev
    You don't put an ORDER BY within the view.
    Do it like this
    Thanks, I tried to do this but it couldn't parse the query.

    I have removed the ORDER BY clause and changed the joins but I still get 939 rows returned.

    Code:
    SELECT DISTINCT qp.FIRST_NAME, qp.SURNAME, qc.COURSE, qc.DESCRIPTION AS Title, qci.ACADEMIC_YEAR
    FROM         QUERCUS..QUERCUS.PERSON_TYPE AS qpt INNER JOIN
                          QUERCUS..QUERCUS.PERSON_DETAIL AS qpd ON qpt.OBJECT_ID = qpd.PERSON_TYPE INNER JOIN
                          QUERCUS..QUERCUS.PERSON AS qp ON qpd.PERSON = qp.OBJECT_ID INNER JOIN
                          QUERCUS..QUERCUS.COURSE_TUTOR AS qct ON qp.OBJECT_ID = qct.PERSON INNER JOIN
                          QUERCUS..QUERCUS.COURSE AS qc ON qct.COURSE = qc.OBJECT_ID INNER JOIN
                          QUERCUS..QUERCUS.COURSE_INSTANCE AS qci ON qc.OBJECT_ID = qci.COURSE
    WHERE     (qpt.DESCRIPTION = 'Tutor')
    I am now wondering if it is to do with the linked server properties, all these tables are in an Oracle database.
    But this is the first time I have had problems creating views with this linked server.
    <- 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
  •