Archives of the TeradataForum

Message Posted: Sun, 18 Aug 2003 @ 02:40:05 GMT

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

Dieter,

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

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.

Thanks,

Bruce

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