 |
 |
Archives of the TeradataForum
Message Posted: Mon, 18 Aug 2003 @ 07:09:18 GMT
| Subj: | | Re: Assigning destination tags to CDRs |
| |
| From: | | Dieter Noeth |
Burton, Bruce wrote:
| | I would say #2 or concatenating a '0000' and '9999' to get start and end ranges (called line ranges in the telco world) and then
using a between (where called_no between trim(prefix||'0000') and trim(prefix||'9999')). I'm definitely not a SQL efficiency guru so I have
no clue if the above would be faster than your #2. I assume loading the prefix table with the line ranges and then doing the join would be
pretty quick since this is how many of the billing engines process call records (use the line range to calc the distance of the call for
pricing, etc.). | |
A join on BETWEEN (like SUIBSTR/LIKE) always results in a product join and this is rather CPU intensive if there are lots of
prefixes.
| | The above assumes your 'prefix' is the npa/nxx (1st 6 digits of the phone #) but I guess you could create npa-000-0000 and npa-
999-9999 table rows to do the same if you only have the npa. A case test on the prefix could dictate when line range to use on the
join...but this seems to get back to your #2 solution. | |
If it's a fixed prefix length, a join on SUBSTR(Called_no, 1, xx) = prefix would be a fast merge join.
| | It gets a little trickier when you get into international traffic and country and city codes...which may not always line up on
the record consistently (sometimes the record is missing the city code, etc.)... but that's a whole different story. | |
And that's what i ment, it's quite ugly. A called_no may match to several prefixes and you'll have to match the longest pattern. I've
seen up to 2000 different prefixes with a varying length from 2 to ~10 chars.
e.g.
Prefix
0049
004930
0049977
Called_no
0049302232233 -> 004930
0049831221 -> 0049
004999712112 -> 0049997
Thanks,
Dieter
| |
