drop/create table no volatil

Database

drop/create table no volatil

Does teradata allow to drop table AND allow re create table by using IF EXIST? the table is not volatil

CREATE MULTISET TABLE TMP_WORK_DB.CR_test ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT
   (
 A_ID  VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
,A_ID_TYPE  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
,CUST_BU_ID  INTEGER
,CUST_NBR  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
,ITM_CLS_CD  VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
,SHIPD_DTS  TIMESTAMP(6)
,ORDER_NBR  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
,ORDER_BUID  INTEGER

    )
PRIMARY INDEX Indx_CR_test(A_ID,CUST_BU_ID,ORDER_BUID,ORDER_NBR,CUST_NBR) ;
DELETE TMP_WORK_DB.CR_test ALL;
---<<<
--<<<_01_INSERT DATA_
---<<<_
INSERT INTO TMP_WORK_DB.CR_test
SELECT .....
....
.... 
FROM TMP_WORK_DB.CR_ORIGINAL;

regards,

Cristina

Tags (1)
4 REPLIES

Re: drop/create table no volatil

can this be possible?

N/A

Re: drop/create table no volatil

Cristina,

If your question is can I check if a table exists, if not then create it, then this will work.

Bteq:

sel * from dbc.tablesv

where databasename = 'YourDB'

and tablename = 'YourTB'

.IF ACTIVITYCOUNT <> 0 THEN .GOTO SKIP_CREATE

CREATE SET TABLE.....

.LABEL SKIP_CREATE

Rglass

Re: drop/create table no volatil

---TEST 
--trying to create table if not exist and delete content if table exist

sel * from dbc.tablesv

where databasename = 'YourDB'

and tablename = 'YourTB'

;
.IF ACTIVITYCOUNT = 0 THEN GOTO CreateNewTable;
.IF ACTIVITYCOUNT <> 0 THEN GOTO CleanTable;
.LABEL CleanTable;
DELETE TMP_WORK_DB.CR_test ALL;

.LABEL CreateNewTable;
--->>>_ DROP TABLE TMP_WORK_DB.CR_test; 
CREATE MULTISET TABLE TMP_WORK_DB.CR_test ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ASST_ID  VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
,ASST_ID_TYPE  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
,CUST_BU_ID  INTEGER
,CUST_NBR  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
 ,RGN_NM_CUST_BUID  VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
)
PRIMARY INDEX Indx_CR_test(ASST_ID,CUST_BU_ID,ORD_BU_ID,ORD_NBR,CUST_NBR); 
DELETE TMP_WORK_DB.CR_test ALL ;
.quit

---DONE

Thanks .. I tried the code above but give me the error>>>>

1 record returned.

select command completed.

error  42000  teradata ODBC    teradata driver     teradata database      syntax error. expected something between the begining of the request and '.'.

command failed

Re: drop/create table no volatil

Probably I am missing something ... I tried to find examples. Could be possible to get some link that give example or tech info..?

thanks in advance