Archives of the TeradataForum

Message Posted: Mon, 18 Aug 2003 @ 12:27:24 GMT

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

Dieter,

interesting question.

When I saw your mail it reminds me on the problem how to give a macro a �unlimited� number of parameter to check for in a table.

The underlying idea of my suggestion is to have two different prefix reference tables. Number one contains per prefix one row with the prefix and the tag you what to set and any other info.

Number two contains only one row and as many columns as the lengths of the prefix can be. Write into each corresponding column all prefixes with the same lengths with a blank before and after the prefix. You can than join the CDR table with the prefix_join table via some like and not like conditions. The benefit of this approach is that you do not have to evaluate which prefix has the biggest lengths (see also SQL's below). What you you think? Is this a reasonable approach?

I am not able to do a volume test with this approach but would be interested in case you try this approach with your data. As only one row for the prefix evaluation is needed I would expect that this row is redistributed to all AMPs and that joined to the CDR's which means a less I/O and more CPU operation.

Kind regards

Ulrich

SQL:
create table cdr
(
msisdn varchar(20)
) unique primary index (msisdn);

insert into cdr values ('0049302000');
insert into cdr values ('0031302001');
insert into cdr values ('0032302001');
insert into cdr values ('0049302001');
insert into cdr values ('0049302101');
insert into cdr values ('0049302200');
insert into cdr values ('0049400000');
insert into cdr values ('0049501043');

create table prefix_join
(
prefix_1 varchar(10000),
prefix_2 varchar(20000),
prefix_3 varchar(34000)
)
unique primary index (prefix_1,prefix_2,prefix_3);

collect stats on prefix_join index (prefix_1,prefix_2,prefix_3);

insert into prefix_join values (' 0049 0031 0032 ',' 004930 004940 ','
00493020 00493021 ');

create table prefix_ref
(
prefix varchar(8),
prefix_desc varchar(100)
)
unique primary index (prefix);

insert into prefix_ref values ('0049','A');
insert into prefix_ref values ('0031','B');
insert into prefix_ref values ('0032','C');
insert into prefix_ref values ('0033','D');
insert into prefix_ref values ('004930','AA');
insert into prefix_ref values ('004940','AB');
insert into prefix_ref values ('004949','AC');
insert into prefix_ref values ('00493020','AAA');
insert into prefix_ref values ('00493021','AAC');
insert into prefix_ref values ('00493099','AAD');

select c.msisdn,
       case when p.prefix_3 like '% ' !! (substring(c.msisdn from 1 for
8)) !! ' %'  then (substring(c.msisdn from 1 for 8))
                when        p.prefix_3 not like '% ' !!
(substring(c.msisdn from 1 for 8)) !! ' %'
                            and
                            p.prefix_2 like '% ' !! (substring(c.msisdn
from 1 for 6)) !! ' %'
                     then (substring(c.msisdn from 1 for 6))
                when      p.prefix_3 not like '% ' !!
(substring(c.msisdn from 1 for 8)) !! ' %'
                            and
                            p.prefix_2 not like '% ' !!
(substring(c.msisdn from 1 for 6)) !! ' %'
                            and
                            p.prefix_1 like '% ' !! (substring(c.msisdn
from 1 for 4)) !! ' %'
                     then (substring(c.msisdn from 1 for 4))
       end as pre,
         r.prefix_desc
from    cdr c,
        prefix_join p,
        prefix_ref r
where (p.prefix_3 like '% ' !! (substring(c.msisdn from 1 for 8)) !! '%'
        or (
            p.prefix_3 not like '% ' !! (substring(c.msisdn from 1 for
8)) !! ' %'
            and
            p.prefix_2 like '% ' !! (substring(c.msisdn from 1 for 6))
!! ' %'
         )
        or (
            p.prefix_3 not like '% ' !! (substring(c.msisdn from 1 for
8)) !! ' %'
            and
            p.prefix_2 not like '% ' !! (substring(c.msisdn from 1 for
6)) !! ' %'
            and
            p.prefix_1 like '% ' !! (substring(c.msisdn from 1 for 4))
!! ' %'
         )
         )
         and r.prefix = pre
order by 1;

drop table cdr;
drop table prefix_join;
drop table prefix_ref;

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