Clarification. is it possible to create table using create/select with format clause from base table.

Database
Enthusiast

Clarification. is it possible to create table using create/select with format clause from base table.

Hi All,

Would like to know whether it is possible to create table using create/select statement which will take format clause from base table.

for example and illustration , say my base table is event where event_id id defined as decimal(38,0) and format clause specified. and i am creating another table say event_tmp using create/select and once created if i view the table structure only datatype is taken from the base table. Is there a way to create event_tmp with format clause.

Reason for doing this is i want to insert records without decimal . since (38,0) is producing results with . append to end.

CREATE MULTISET TABLE event ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
event_id DECIMAL(38,0) FORMAT '--(37)9')
UNIQUE PRIMARY INDEX XUPI_event ( event_id );

CREATE MULTISET TABLE event_tmp
AS (SELECT DISTINCT event_id
FROM event
) WITH DATA PRIMARY INDEX (event_id);

CREATE MULTISET TABLE event_tmp ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
event_id DECIMAL(38,0))
PRIMARY INDEX ( event_id );

thanks
2 REPLIES
Junior Contributor

Re: Clarification. is it possible to create table using create/select with format clause from base table.

You have to add the format to your create:

CREATE MULTISET TABLE event_tmp
(event_id FORMAT '--(37)9')
AS (SELECT DISTINCT event_id
FROM event
) WITH DATA PRIMARY INDEX (event_id);

Btw, why do you want MULTISET/NUPI for the result of a DISTINCT?

Dieter
Enthusiast

Re: Clarification. is it possible to create table using create/select with format clause from base table.

Hi Dieter, Thanks a lot... It is not the real time scenario ..table structure is for illustration purpose..