Replace View ( DDL ) inside a Stored Procedure Teradata

Database
Enthusiast

Replace View ( DDL ) inside a Stored Procedure Teradata

Hi All,

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.

Logic :

Macro (

Delete Tab1;

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 ) )

Delete Tab;

Insert into Tab from Tab2;

-- Replace view ( Need to replace the Tab2 Table with Tab Table ( Reverting back to original ))

Delete Tab2;

) End of Macro.

Please let me know if the above scenario can be achieved without Bteq.

Thanks in Advace.

Karthick.

3 REPLIES
Enthusiast

Re: Replace View ( DDL ) inside a Stored Procedure Teradata

Hi All..

Any Suggestions?

Thanks in Advance

Karthick.

Junior Contributor

Re: Replace View ( DDL ) inside a Stored Procedure Teradata

Hi Karthick,

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.

Enthusiast

Re: Replace View ( DDL ) inside a Stored Procedure Teradata

Hi Karthik,

DDL can be used in Macro with these limitations:

  • Only ONE DDL in a macro
  • DDL should be the last statement in Macro

So, you can replace one view in one macro as last statement of macro. Anything after this DDL would cause error and macro failure.