Archives of the TeradataForum

Message Posted: Sun, 21 Jul 2002 @ 15:14:37 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Claybourne,

Here a solution that uses a like construct. It overcome the problem of Michael and can be used for different data types. In case of numeric data types you have to be careful by specifying your select list - see example for the decimal selection. Here you have to specify 1.00 to get an result, 1 does not return a row. I am sure that you even solve this problem if you know which data type is column has and which input formats you like to allow. In case of an character field you have to be sure that the delimiter is not in any row you like to select - this will causes wrong results.

Kind regards

Ulrich

SQL example:

Create table tab
(
        a_dec decimal(15,2),
        a_int integer,
        b_char varchar(20)
);

insert into tab values (1,1,'sdas');
insert into tab values (11,11,'sas');
insert into tab values (12,12,'sdas');
insert into tab values (123,123,'s');
insert into tab values (23,23,'teras');
insert into tab values (12221,12221,'sas');
insert into tab values (21,21,'asdsasd');
insert into tab values (12.34,13,'asdd das');
insert into tab values (12.12,14,'das');

replace macro sel_dec
( a (varchar(31998))
) as
(select a_dec
 from tab
 where ( ',' !! :a !! ',') like '%,' !! trim(a_dec) !! ',%'
;)
;

exec sel_dec ('1');
exec sel_dec ('1.00');
exec sel_dec ('12.12,12.00,1.00,23.00,12.34,11.00');

replace macro sel_int
( a (varchar(31998))
) as
(select a_int
 from tab
 where ( ',' !! :a !! ',') like '%,' !! trim(a_int) !! ',%'
;)
;

exec sel_int ('1');
exec sel_int ('1,11,13,12,21');

replace macro sel_char
( a (varchar(31998))
) as
(select b_char
 from tab
 where ( ',' !! :a !! ',') like '%,' !! trim(b_char) !! ',%'
;)
;

exec sel_char ('s');
exec sel_char ('sas,asdd das');
exec sel_char ('sas,asdd das,das');

drop macro sel_dec;
drop macro sel_int;
drop macro sel_char;
drop table tab;

	<Prev	Next>		<<First	<Prev	Next>	Last>>