Archives of the TeradataForum

Message Posted: Thu, 13 Mar 2008 @ 11:32:08 GMT


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

Subj:		Re: Can I avoid a product join?

From:		Dieter Noeth

Bob Duell wrote:

I have a 2,000 row table (DEV_LOOKUP) based on a non-overlapping range of serial numbers (column names beg_serial_num and end_serial_num, which make up the unique primary index). The table contains attributes for equipment sold to our customers, such as make and model number.

I also have a 50 million row table (CUSTOMER) with the unique serial number of a particular device assigned to individual customers. To lookup the attributes of a particular device, I currently join these two tables using a BETWEEN expression, i.e.

I modified (and tested) my query, this is the best i can think of:

     SELECT
         lkup.model_num
       , items_in_use
     FROM
       (
        SELECT
           beg_serial_num
         , COUNT( *) - 1 AS items_in_use
        FROM
         (
          SELECT
             serial_number

     -- find the matching beg_serial_num
           , MAX(
                CASE
                   WHEN x = 0
                   THEN serial_number
                END) OVER (ORDER BY serial_number, x
                           ROWS UNBOUNDED PRECEDING) AS beg_serial_num
          FROM
           (
            SELECT
               1 AS x  -- indicator if serial_number or beg_serial_num
             , serial_number
            FROM
               customer2 AS c

            UNION ALL

            SELECT
               0 AS x
             , beg_serial_num
            FROM
               DEV_LOOKUP
           ) AS dt
         ) AS dt
        GROUP BY 1
       ) AS dt
     JOIN DEV_LOOKUP AS lkup
     ON lkup.beg_serial_num = dt.beg_serial_num

This query returns items_in_use = 0 for new model_num, to get rid of it add a WHERE items_in_use > 0

I didn't care about the end_serial_num, because hopefully there's no serial_number out of range and you probably just added that to ease the join.

Dieter


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

Attachments

Library

Quick Reference