 |
 |
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
| |
