Creating a Temporary Table in SQL Navigator 6.5

SQL Navigator
1

DummyField1 FLOAT,

DummyField2 FLOAT,

a.JURISDICTIONCODE as DummyField1,

SELECT

a.NAME DummyField2,

to_number(REGEXP_REPLACE(REGEXP_REPLACE(b.value,’[a-zA-z]’,’’),’,’,’’)) as DummyField3

INTO #myTempTable

FROM

DLSGATEWAY.JURISDICTION a,

DLSGATEWAY.SE_DATA b,

DLSGATEWAY.SE_DATA_VALUE_TYPE c

WHERE

AND b.VALUE_TYPE_ID=c.VALUE_TYPE_ID

However, I keep getting erroe messages. I know the ‘#’ sign is reserved for SQL Server 2005 and such, but it’s the only coding I could find in my google search. Any help is much appreciated. Thanks!

Hello all,

First time POSTER. I’m trying to create a temp table with the following syntax…

CREATE TABLE #myTempTableDummyField3 FLOAT****************b.JURISDICTION_CODE=a.JURISDICTIONCODE

2

Yes, # isn’t used at all and Oracle handles the column definitions based on the source query. IMHO : Get rid of all mixed case in your names - they are a nightmare to manage. This should work fine:

CREATE TABLE MYTEMPTABLE
AS
SELECT
A.JURISDICTIONCODE DUMMYFIELD1,
A.NAME DUMMYFIELD2,
TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(B.VALUE,’[A-ZA-Z]’,’’),’,’,’’)) DUMMYFIELD3
FROM
DLSGATEWAY.JURISDICTION A,
DLSGATEWAY.SE_DATA B,
DLSGATEWAY.SE_DATA_VALUE_TYPE C
WHERE B.JURISDICTION_CODE=A.JURISDICTIONCODE
AND B.VALUE_TYPE_ID=C.VALUE_TYPE_ID

3

Hi,

You basically answered my queston so thank you! But I have a few more…

How can I drop the table once I’m down with it? I think you simply write code and you’re good, correct?

Also, where is the temp table stored? I have a permanent table list in my database, so it obviously wouldn’t be there. I was just wondering if it gets dropped somewhere else so when I’m ready to use it, it’s easily retrieved.

Thanks

Mike B

4

In Navigator you can drop any object by right clicking on it and selecting DROP from the menu items.

Sorry, the code I supplied builds a “regular” table. To build a temp table just modify the code based on this (unrelated) example :

CREATE GLOBAL TEMPORARY TABLE ONTIME_CALLOUT_PLANS_TTBL
(PLAN_KEY VARCHAR2(4001),
START_DATE DATE)
ON COMMIT PRESERVE ROWS
NOPARALLEL
/

In Oracle you define the table and then populate/manipulate the data via code.

Here’s an asktom thread that answers every conceivable question about them (and the differences between SQL Server and Oracle) : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48812348054

Once you create the temp table it will appear in the table list with the “regular” tables. As you’ll read it’s because in Oracle you define table statically and then use it as needed instead of building/rebuilding it on the fly. Incidentally, since they’ll appear with your regular tables it’s also why a lot of people use specific prefex/suffix for temp tables to help keep them straight (here, I use _TTBL).

Once built/defined, you can use just like any table (generally via PL/SQL objects). That is, you’ll populate the data as needed.

5

Hmmmm, I cant find the “regular” table I created in my table list. I created the “regualr” table before I saw your second post haha. I’m planning to drop it, but can’t find it. I’m refreshing, but nothing’s coming up. Weird.

Is there code I can use to drop it?

6

Ok, I figured out how to DROP the table. I was coding DROP mytemptable, when I needed to ID what I was dropping (VIEW, TABLE, etc). So DROP TABLE mytemptable is the correct syntax.

Obviously I won’t be able to located the table anywhere now, but I’m still curious/confused on the location of the table once I create it. I agree that I should see it in the table list, but I don’t. Dammit all! haha.

7

If it didn’t appear I suspect you didn’t commit the table. Did you issue a COMMIT either through code or clicking the COMMIT icon?

8

CREATE TABLE MYTEMPTABLE

AS

SELECT

A.JURISDICTIONCODE as Dummyfield1,

A.NAME as Dummyfield12,

TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(B.VALUE,’[A-ZA-Z]’,’’),’,’,’’)) as Dummyfield3

FROM

DLSGATEWAY.JURISDICTION A,

DLSGATEWAY.SE_DATA B,

DLSGATEWAY.SE_DATA_VALUE_TYPE C

WHERE

AND b.VALUE_TYPE_ID=c.VALUE_TYPE_ID

AND b.YEAR = 2010

Ahhhh, I definietly did NOT use the COMMIT command or click the button. Where in the syntax would the COMMIT clause go?..at the end?

****************b.JURISDICTION_CODE=a.JURISDICTIONCODEAND c.VALUE_TYPE_ID = 51

9

Yes, either add it to the end of the statement :

CREATE TABLE blah blah blah ;

COMMIT;

or press the commit icon (db icon with a green check mark).

10

Ahh, alright man, I’ll check this out. Thanks for your help. I think you’ve answered my question. I’ll post back if not haha.

Mike B