Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 12 Mar 2008 @ 23:45:41 GMT


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


Subj:   Re: Can I avoid a product join?
 
From:   Dieter Noeth

Bob Duell wrote:

  Can anyone suggest an alternative technique? I have lots of flexibility with the DEV_LOOKUP table (I own it), but the main CUSTOMER table is outside my control.  


Instead of Cross-Joining you might use OLAP-functions to assign the beg_serial_num to each serial_number:

     SELECT
        b.model_num
     , COUNT(*) AS items_in_use
     FROM
       (
        SELECT
          serial_number
        , 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
          , serial_number
          FROM customer2 AS c

          UNION ALL

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

This is returning the same result as your BETWEEN join, but i'm too tired now to explain that approach in detail, maybe you should run it step by step using some sample data.

I just tested it using some small tables on my notebook, so i don't know if it's better than a cross join. At least it's more complicated and there are more steps in explain ;-)


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: 23 Jun 2019