use macro variable in view name in create view statement

Database
Fan

use macro variable in view name in create view statement

I am trying to create a macro which I will use to create views each month like so...

create macro jt_m (viewmth char(11), mth char(6)) as
(
create view :viewmth as
select * from
view1 where datamth = :mth ;
);

..but it doesn't seem to like having a macro variable as a view name. Is there another way of doing this..?

2 REPLIES
Enthusiast

Re: use macro variable in view name in create view statement

You can't generate a DDL dynamically from macro.

What you need is a stored procedure with dynamic sql to generate the view definition using parameters...

something like this ......

REPLACE PROCEDURE GEN_VIEW(IN viewmth char(11), IN mth char(6))
BEGIN
CALL DBC.SysExecSQL('create view ' || viewmth ||' as select * from view1 where datamth = ''' || mth || ''';' );
END

Also remember with dynamic sql you need to create the proc under the same user id database as the user id used to create the proc. and yes, you would need that user id to have perm space, since stored procedures take up perm space.

Re: use macro variable in view name in create view statement

Hi.

I have a problem.

I have a cursor with a select statement.

The select statement returns 5 rows.

Row 1-- Select col 1

Row 2 --,col2

Row3---,col3

Row4---,col4

Row5--- From table A;

How do I create a view for this select statement

using stored procedure dynamic sql?

- Shraddha.