Results 1 to 4 of 4

Thread: Temp Tables

  1. #1
    Join Date
    Aug 2008
    Posts
    10

    Unanswered: Temp Tables

    We are in the process of moving from MSSQL and Sybae to Oracle and I'm converting our existing SQL.

    We've used temp tables in some queries and I'm curious how to convert it to Oracle. For example:

    create table #Scores(Student_Number float, TestName varchar(35), Score float) --create temp table
    insert into #Scores -- Populate temp table

    select s.Student_Number,ts.name,max(sts.NumScore) as Score
    from Students s
    inner join StudentTestScore sts on sts.studentid = s.id
    inner join TestScore ts on ts.id = sts.testscoreid
    where
    (ts.Name in('Math','Reading','Writing') and ts.TestID in (3,101,102))
    and s.Enroll_Status = 0
    and sts.NumScore >0
    and s.grade_level > 9
    group by
    s.Student_Number,ts.name
    --end populate temp table

    insert into #Scores -- Populate temp table
    select s.Student_Number,'Test',s.Balance4 as Score
    from Students s
    where
    s.Enroll_Status = 0
    and s.grade_level > 9
    group by
    s.Student_Number
    --end populate temp table

    select
    Student_Number,
    TestName,
    Score
    from
    #Scores
    where
    Score <=159
    order by
    Student_Number, TestName

    --remove temp table Scores
    drop table #Scores

    -----------------------------------------------------------------
    How should this be done for Oracle?
    Last edited by Opus; 09-15-08 at 13:01.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Only once

    In Oracle you create the temp table(s) only ONCE and then just use it:
    Code:
    CREATE GLOBAL TEMPORARY TABLE Scores
       ON COMMIT PRESERVE ROWS 
      (Student_Number NUMBER
      ,TestName VARCHAR2(35)
      ,Score NUMBER);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One point is that you should try and avoid using temp tables where you can. For the example you provide you could easily do it one query which is more efficient and lets the optimiser do more sophisticated optimisations using indexes.

    Alan

  4. #4
    Join Date
    Aug 2008
    Posts
    10
    That'll do it. Thanks

Posting Permissions

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