HI, my code is as below:
CREATE MULTISET VOLATILE TABLE t1,NO LOG AS
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!
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;
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?
ALTER PROCEDURE [dbo].[SpecialInfo]
where COL_ID = @COL_ID
select *, DeletedFlag = 1
select *, DeletedFlag = 0
where cid in (select cid from #DeletedInfo)
This is a source code that i try to convert
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.
i did it this way:
thank you for your help!! it was very useful!!!
create multiset volatile table tt, no log
CREATE PROCEDURE SpecialInfo
DYNAMIC RESULT SETS 1
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;
declare v1 cursor with return only for
select tt_DeletedInfo.*, 1 AS DeletedFlag
select SpecialInfo.*, 0 AS DeletedFlag
where cid in(select cid from tt_DeletedInfo);