variable

[dupa12345]

how can i define and use variables?

I have a query with a bunch of hardcoded dates. i'd like to replace them with a variable, which i can define before the query. I'm running a simple query batch.

%var1% = '2001-01-01'
select * from t1 where dt = %var1%

something like this.

 

[tdatgod]

Hi,
you can make a macro out of your query and then pass in you date as a parameter.



CREATE MACRO FindRecords AS (mydate date)
(SELECT * from t1 where dt = :mydate;

then you execute it by

execute FindRecords(2001-01-01);

 

[dupa12345]

thx this maybe actually solve more then i asked for
after i create a macro where does it reside and how long does it stay there, is this a one time deal or can it be stored for x time

question 2
if date is not specified can it assume current date somehow?

 

[tdatgod]

Hi,
Create Macro is like Create Table it stays there until you explicitly Drop Macro or Drop Database it resides in.


It resides in your Database space

Bob.FindRecords

but doesn't really take any of your assigned disk space because it lives in the Dictionary tables.


If some one else wants to execute your macro they must specify your database name.

execute bob.FindRecords('2001-01-01');

or

database bob;
execute FindRecords(currentdate);


You might also have to GRANT execute privledge to your macro if your database isn't already public.

Grant Execute on FindRecords to james;

refrain from

Grant Execute on FindRecords to public;

until you clear it with your DBA. The Public grant could take hours to run and it will lock the dictionary and most jobs on the production system will grind to a halt.



You can assign a default if the user doesn't specify a value like....

CREATE MACRO FindRecords AS
(mydate date default currentdate)
(SELECT * from t1 where dt = :mydate;