I am migrating from Informix SE to Oracle 9i and attempting to learn Oracle SQL as I go. I am currently translating SQL which is read by a reporting tool which imposes a few constraints: I am allowed multiple SQL statements but the last statement must return the data that will construct the report.
Many of the reports require a year-to-date, quarter-to-date and month-to-date summary of sales data held in two tables - order headers and line items. The typical current implementation uses two queries to generate the mtd and qtd summaries and store them in temporary tables using an INTO TEMP clause. The last query joins these two temporary tables to the main tables, extracting year-to-date summaries alongside the stored ytd and qtd figures.
There are three key factors which I need to consider: muliple copies of one report may be running simultaneously; muliple reports may be running simultaneously; many different reports use the same temporary table names (mtd and qtd) with differences in structure.
I have researched Oracle temporary tables (CREATE TABLE tempname AS ...) and cannot see how to use them in this context. The should be created once per database but that seems to tie a structure to each name, requiring many of the temporary tables to be renamed and imposing a constraint on the report authors to avoid re-using existing names. I cannot drop the tables after each use as the last statement must be the one returning the data to the calling environment. I believe that I would also have problems if two reports were running simultaneously using the same name for tables with differing structures.
My questions:
1. Is there a way of creating temporary tables in Oracle whose name and structure exist only for the duration of the session and provide complete isolation from other sessions?
2. Are temporary tables the wrong tool to use in Oracle: Is there a better facility to use for this sort of intermediate data storage?
3. Is there some sort of multiple self-join that I can use to extract the data I need in a single query. Would this be significantly better or worse in performance terms?
Here's a hugely simplified example of the original Informix SQL which does exactly what I want. I hope that the schema are easily guessed.
Yours,
fish
["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur
Many of the reports require a year-to-date, quarter-to-date and month-to-date summary of sales data held in two tables - order headers and line items. The typical current implementation uses two queries to generate the mtd and qtd summaries and store them in temporary tables using an INTO TEMP clause. The last query joins these two temporary tables to the main tables, extracting year-to-date summaries alongside the stored ytd and qtd figures.
There are three key factors which I need to consider: muliple copies of one report may be running simultaneously; muliple reports may be running simultaneously; many different reports use the same temporary table names (mtd and qtd) with differences in structure.
I have researched Oracle temporary tables (CREATE TABLE tempname AS ...) and cannot see how to use them in this context. The should be created once per database but that seems to tie a structure to each name, requiring many of the temporary tables to be renamed and imposing a constraint on the report authors to avoid re-using existing names. I cannot drop the tables after each use as the last statement must be the one returning the data to the calling environment. I believe that I would also have problems if two reports were running simultaneously using the same name for tables with differing structures.
My questions:
1. Is there a way of creating temporary tables in Oracle whose name and structure exist only for the duration of the session and provide complete isolation from other sessions?
2. Are temporary tables the wrong tool to use in Oracle: Is there a better facility to use for this sort of intermediate data storage?
3. Is there some sort of multiple self-join that I can use to extract the data I need in a single query. Would this be significantly better or worse in performance terms?
Here's a hugely simplified example of the original Informix SQL which does exactly what I want. I hope that the schema are easily guessed.
Code:
SELECT head.cust AS cust, SUM(item.value) AS value
FROM head, item
WHERE head.ref = item.ref
AND YEAR(head.date) = 2006
AND MONTH(head.date) = 8
INTO TEMP mtd;
SELECT head.cust AS cust, SUM(item.value) AS value
FROM head, item
WHERE head.ref = item.ref
AND YEAR(head.date) = 2006
AND MONTH(head.date) IN ( 7, 8, 9 )
INTO TEMP qtd;
SELECT head.cust, SUM(item.value) AS ytdval, qtd.value, mtd.value
FROM head, item, OUTER ytd, OUTER mtd
WHERE head.ref = item.ref
AND YEAR(head.date) = 2006
AND head.cust = ytd.cust
AND head.cust = mtd.cust
INTO TEMP qtd;Yours,
fish
["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur
![[profile]](/data/assets/smilies/profile.gif "[profile] [profile]")