 |
 |
Archives of the TeradataForum
Message Posted: Mon, 09 Aug 2004 @ 14:11:05 GMT
| Subj: | | Re: Using a parameter in a macro |
| |
| From: | | teradatanewsgroup |
Hi,
this works in case you really can pass only one parameter - but be arware of the problems in case you get wrong inputs like exec sel_age
('>A90');
Kind regards
Ulrich
replace macro sel_age
(
in_var varchar(100)
) as (
ABORT 'Wrong comparison operator has to be =, >, <, >= or <='
WHERE substr(:in_var,1,(case when substr(:in_var,2,1) = '=' then = 2 else 1 end)) not in ('=','>','<','>=','<=');
select *
from Table_with_age
where (case
when substr(:in_var,1,(case when substr(:in_var,2,1) = '='
then 2 else 1 end)) '=' then
case when age = cast((substr(:in_var,(case when
substr(:in_var,2,1) = '=' then 3 else 2 end))) as integer)
then 1 else 0 end
when substr(:in_var,1,(case when substr(:in_var,2,1) = '='
then 2 else 1 end)) = '<' then
case when age < cast((substr(:in_var,(case when
substr(:in_var,2,1) = '=' then 3 else 2 end))) as integer)
then 1 else 0 end
when substr(:in_var,1,(case when substr(:in_var,2,1) = '='
then 2 else 1 end)) = '>' then
case when age > cast((substr(:in_var,(case when
substr(:in_var,2,1) = '=' then 3 else 2 end))) as integer)
then 1 else 0 end
when substr(:in_var,1,(case when substr(:in_var,2,1) = '='
then 2 else 1 end)) = '>=' then
case when age >= cast((substr(:in_var,(case
when substr(:in_var,2,1) = '=' then 3 else 2 end))) as integer)
then 1 else 0 end
when substr(:in_var,1,(case when substr(:in_var,2,1) = '='
then 2 else 1 end)) = '<=' then
case when age <= cast((substr(:in_var,(case
when substr(:in_var,2,1) = '=' then 3 else 2 end))) as integer)
then 1 else 0 end
else 0
end) = 1
order by age
;
);
--
Ulrich Arndt
| |
