Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    10

    Unanswered: Re-writing SQL statement

    I'm not looking to have someone rewrite the statement below, just seeking advice as how to approach it. I'm new to PL/SQL but have experience with Sybase and MSSQL Server.

    In Sybase, the temp tables would have been created in the Temp db and then discarded. With the statement below, the temp tables are truncated after the statement is run but the table still exist. I added two "Drop Table" statements at the end of the script, but I received a message indicating the table were in use.

    This is an issue since the database is part of a proprietary application and we are not suppose to be created any objects within the database.

    I tried using Transaction-specific temp tables but that didn't work.

    Code:
    -- Select Cactus ID ------------------------------------------------------------
    CREATE GLOBAL TEMPORARY TABLE tch1
       (name Varchar(45), tid Int, CactusID Varchar(12))
    ON COMMIT PRESERVE ROWS;
    
    INSERT INTO tch1
        SELECT
            t.lastfirst,
            t.id teacherid,
            c.value
        FROM
            teachers t
            INNER JOIN customvarchars c ON t.id = c.KeyNo 
            AND fieldno = 148;
    
    -- Select Core Codes ---------------------------------------------------------------
    CREATE GLOBAL TEMPORARY TABLE crs1
       (name Varchar(45), cid Int, CactusCd Varchar(12))
    ON COMMIT PRESERVE ROWS;
    
    INSERT INTO crs1
    
        SELECT
            cs.course_name,
            cs.id courseid,
            c.value
        FROM
            Courses cs
            INNER JOIN customvarchars c ON cs.id = c.KeyNo 
            AND fieldno = 146;
    
    -- Put it all together ---------------------------------------------------------
    SELECT
        t.lastfirst AS Teacher,
        ct.CactusID,
        s.schoolid AS Sch#,
        s.TermID,
        c.course_number AS Crs#,
        s.section_number AS Sec#,
        c.course_name AS Course,
        cr.CactusCd,
        COUNT(*) AS Enroll
        
    FROM
        cc
        INNER JOIN sections s ON cc.sectionid = s.id
        INNER JOIN courses c ON s.course_number = c.course_number
        LEFT JOIN crs1 cr ON c.id = cr.cid
        INNER JOIN teachers t ON s.teacher = t.id
        LEFT JOIN tch1 ct ON t.id = ct.tid
    
    WHERE s.schoolid NOT IN (701,711)
    
    GROUP BY
        s.schoolid,
        t.lastfirst,
        ct.CactusID,
        c.course_number,
        s.section_number,
        c.course_name,
        cr.CactusCd,
        s.TermID
    
    ORDER BY
        s.schoolid,
        t.lastfirst,
        ct.CactusID,
        c.course_number,
        s.section_number,
        c.course_name,
        cr.CactusCd,
        s.TermID;
    Last edited by Opus; 09-17-08 at 11:58.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In Oracle, temporary tables are to be used very rarely, if ever - and as you have discovered, the tables are not temporary, rather the data in them is temporary.

    This case seems far too simple to require temporary tables anyway. Try this:
    Code:
    WITH tch1 AS 
    (
        SELECT
            t.lastfirst,
            t.id teacherid,
            c.value
        FROM
            teachers t
            INNER JOIN customvarchars c ON t.id = c.KeyNo 
            AND fieldno = 148
    ),
    crs1 AS
    (
        SELECT
            cs.course_name,
            cs.id courseid,
            c.value
        FROM
            Courses cs
            INNER JOIN customvarchars c ON cs.id = c.KeyNo 
            AND fieldno = 146
    )
    SELECT
        t.lastfirst AS Teacher,
        ct.CactusID,
        s.schoolid AS Sch#,
        s.TermID,
        c.course_number AS Crs#,
        s.section_number AS Sec#,
        c.course_name AS Course,
        cr.CactusCd,
        COUNT(*) AS Enroll
        
    FROM
        cc
        INNER JOIN sections s ON cc.sectionid = s.id
        INNER JOIN courses c ON s.course_number = c.course_number
        LEFT JOIN crs1 cr ON c.id = cr.cid
        INNER JOIN teachers t ON s.teacher = t.id
        LEFT JOIN tch1 ct ON t.id = ct.tid
    
    WHERE s.schoolid NOT IN (701,711)
    
    GROUP BY
        s.schoolid,
        t.lastfirst,
        ct.CactusID,
        c.course_number,
        s.section_number,
        c.course_name,
        cr.CactusCd,
        s.TermID
    
    ORDER BY
        s.schoolid,
        t.lastfirst,
        ct.CactusID,
        c.course_number,
        s.section_number,
        c.course_name,
        cr.CactusCd,
        s.TermID;

  3. #3
    Join Date
    Aug 2008
    Posts
    10
    Excellent! That is what is called CTE (Common Table Expression) in MS SQL Server. I was hoping Oracle had something like it, I just didn't know what it was called. You made my day.

  4. #4
    Join Date
    Aug 2008
    Posts
    10
    Never mind.
    Last edited by Opus; 09-17-08 at 13:42.

Posting Permissions

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