Archives of the TeradataForum

Message Posted: Wed, 12 Mar 2008 @ 23:44:59 GMT

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

Interesting approach, why isn't the customers' product holding(s?) identified by a product ID?

If it were me, I would get the DBA to add a product holdings table (with FK of customer ID and FK to product table).

Anyway one other solution is to introduce the concept of a product holdings table generated from the serial number ranges. Assuming you don't already have one.

Add a column to your DEV_LOOKUP table called PID (or Serial_num_group or whatever is meaningful). Initialise it to a sequential number. Use a csum (1,1) type query, or create a new table with the new column as "Identity Generated always" and insert everything from your current table into the new one. Bottom line, get some unique values into this dev_lookup table that can be used in an equi-join.

Create a second table (SER_PROD_MAP or whatever) that sits between the customer and dev_lookup table. This has two columns the serial number (from customer) and the newly created column (Serial_Num_Group). Use a variant of your product join to populate this middle table so that the individual serial numbers in your customer table map to the generated numbers in the dev_lookup table.

Example

     Customer1.serial_number = 1234
     Customer2.serial_number = 1587
     DEV_LOOKUP. Serial_begin = 1000
     Dev_lookup.serial_end = 2000

New fields

     Dev_Lookup.serial_num_group (for the 1000->2000 range) = 42

     SER_PROD_MAP (for cust1). Serial_number = 1234
     SER_PROD_MAP (for cust1). Serial_num_group = 42
     SER_PROD_MAP (for cust2). Serial_number = 1587
     SER_PROD_MAP (for cust2). Serial_num_group = 42

Now you can do inner (equi-joins) from customer to dev_Lookup and probably avoid the product joins. You will have to live with the product join when you populate the middle table, but now this is a once off. You will need to periodically maintain the middle table, you could do it incrementally (I.e. only customers who have changed go through the product join and thus it is less expensive) or do a full refresh.

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