Archives of the TeradataForum

Message Posted: Wed, 05 Mar 2008 @ 11:19:39 GMT

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

I've got a table of Account Numbers, which has a history of those Account Numbers being renumbered to a new number, for a number of customers. This can occur a number of times for the same actual customer and I need to be able to chain them together, which I assumed a Recursive query would ideal.

Here's what I've got on the table :

     TABLE: ACCOUNT_RENUM
     ACCOUNT_NUMBER_NEW            integer
     ACCOUNT_NUMBER_OLD            integer

Simple as that !

This table also contains the entry WHERE ACCOUNT_NUMBER_NEW=ACCOUNT_NUMBER_OLD, which is then considered the most recent account number for the customer i.e. Level 0

The way I see it, the most recent Account number is the Parent, with a history of child entries.

No two entries can share the same Parent account, so it's not quite a Bill of Materials explosion.

When I code it, the answers fine as long as I restrict the query to one Account number. However, if I run it for all entries on ACCOUNT_RENUM WHERE ACCOUNT_NUMBER_NEW=ACCOUNT_NUMBER_OLD (as the 'seed' statement) the query ends in numeric overflow when I use LEVEL+1 in the 'recursive statement' - and there aren't many rows on my test table, so I must be doing something wrong.

Is this a simple query to code for anyone with experience of Recursive queries ?

I won't include my query, because I'm just getting nowhere fast.

Thanks,

Dave Clough
Database Designer
Express ICS

www.tnt.com

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