Could you please let me know whether stored procedure can be created in teradata only having a replace view statement and without any parameters since we dont have any parameters.
Also guide me whether a macro having a replace view statement can used inside another macro.
We are having view switching requirement here and unable to use the Replace View inside a macro.
Insert Tab1 from View1;
Insert into Tab2 from Tab;
Delete Tab2 where condition;
Insert into Tab2 from Tab1;
Delete from Tab2 where condition;
-- Replace view ( Need to replace the Tab Table with Tab2 Table ( pointing view to Temp Table ) )
Insert into Tab from Tab2;
-- Replace view ( Need to replace the Tab2 Table with Tab Table ( Reverting back to original ))
) End of Macro.
Please let me know if the above scenario can be achieved without Bteq.
Thanks in Advace.
DDL and DML can't be submitted within the same transaction.
MACROs are multi statements, i.e. one transaction, so there's no way to do what you want in a macro.
Of course you can do this easily in a SP.
DDL can be used in Macro with these limitations:
So, you can replace one view in one macro as last statement of macro. Anything after this DDL would cause error and macro failure.