Archives of the TeradataForum

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

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

Bob Duell wrote:

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>>