Archives of the TeradataForum
Message Posted: Wed, 05 Mar 2008 @ 11:19:39 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|