Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023