alter table with volatile table error

Training
N/A

alter table with volatile table error

HI, my code is as below:

 

CREATE MULTISET VOLATILE TABLE t1,NO LOG AS

(

select *

from tt_DeletedInfo) WITH DATA ON COMMIT PRESERVE ROWS;

 

alter table t1 add DeletedFlag default '1';

 

but on executinh i get error: Volatile table 't1' not allowed in statement. 

 

what's wrong, please help!

 

 

5 REPLIES
N/A

Re: alter table with volatile table error

You simply can't ALTER a Volatile Table.

Why don't you add that column during creation?

CREATE MULTISET VOLATILE TABLE t1,NO LOG AS
(
select t.*, '1' as DeletedFlag
from tt_DeletedInfo as t) WITH DATA ON COMMIT PRESERVE ROWS;
N/A

Re: alter table with volatile table error

I don't create this table, it's a subquery from another table and i need to add to it an column with default value '1'

 

is there any other option in teradata? 

N/A

Re: alter table with volatile table error

ALTER PROCEDURE [dbo].[SpecialInfo]

@COL_ID int

 

select *

into #DeletedInfo

from SpecialInfo

where COL_ID = @COL_ID

 

select *, DeletedFlag = 1

from #DeletedInfo

union all

select *, DeletedFlag = 0

from SpecialInfo

where cid in (select cid from #DeletedInfo)

 

This is a source code that i try to convert

 

N/A

Re: alter table with volatile table error

There's no ALTER TABLE in your source code :-)

 

If this is the full source you probably don't need a temp table, you might do a simple macro instead:

REPLACE MACRO SpecialInfo_m (COL_ID INT) AS
 (
   WITH #DeletedInfo AS
    ( SELECT *
      FROM SpecialInfo
      WHERE COL_ID = :COL_ID
    )
   SELECT t.*, 1 AS DeletedFlag
   FROM #DeletedInfo AS t
   UNION ALL
   SELECT t.*, 0 AS DeletedFlag
   FROM SpecialInfo AS t
   WHERE cid IN 
    (SELECT cid FROM #DeletedInfo);
);
-- then EXECute instead of CALL
exec SpecialInfo_m (111);

As a SP it's more complicated, you need to define DYNAMIC RESULT SETS 1 and wrap the Select in a cursor. 

N/A

Re: alter table with volatile table error

i did it this way:

 

thank you for your help!! it was very useful!!!

 

database CampaignOperatorsDB; 

create multiset volatile table tt, no log

(v_COL_ID INTEGER);

 CREATE PROCEDURE SpecialInfo

 

(v_COL_ID INTEGER)

 

DYNAMIC RESULT SETS 1

BEGIN

 

 

CREATE MULTISET VOLATILE TABLE tt_DeletedInfo,NO LOG AS

(

select * from SpecialInfo

where COL_ID = tt.v_COL_ID) WITH DATA ON COMMIT PRESERVE ROWS;

 

 

begin

declare v1 cursor with return only for

select tt_DeletedInfo.*, 1 AS DeletedFlag

from tt_DeletedInfo

union all

select SpecialInfo.*, 0 AS DeletedFlag

from SpecialInfo

where cid in(select cid from tt_DeletedInfo);

 

 

open v1;

end;

END;