RE: Temporary tables

[Holger Becker]

Alejandro D. Burne wrote:

> Thanks, now I've another question, I don't know if here is the right place to do
> this, if not just tell me.
>
> I want to create a temporary table and make a primary key on it (unique index may
> work also) but makes an error.
> 
> CREATE TABLE temp.a AS
> SELECT field1, field2 FROM table1 WHERE...
> //
> ALTER TABLE temp.a ADD PRIMARY KEY (field1)
> //
> ---- Error -------------------------------
> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> General error;-9205 POS(1) System error: AK Catalog information not
> found:0000000000000004004100.
> alter table temp.a add primary key (field1)
> (field1 is pk on table 1)

This is a bug and will be fixed with one of the next versions.
Thank you for reporting it.

As a workaround you could define the primary key at table creation, 
e.g.

CREATE TABLE temp.a (primary key (field1))
AS SELECT field1, field2 FROM table1 WHERE...


> or:
> 
> CREATE TABLE temp.a AS
> SELECT field1, field2 FROM table1 WHERE...
> //
> CREATE INDEX ix1 ON temp.a (field1)
> //
> ---- Error -------------------------------
> to Commit: On, SQL Mode: Internal, Isolation Level: Committed
> Syntax error or access violation;-5001 POS(26) Missing privilege:INDEX.
> create index ix1 on temp.a (field1)

This is a feature because SAP DB doesn't allow indexes on temp tables.

Best regards,
Holger
SAP Labs Berlin