Archives of the TeradataForum

Message Posted: Mon, 26 Sep 2005 @ 12:16:47 GMT

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

ulrich arndt wrote:

With some minor modifications (see below) it's fast (but not as fast as the second), but this one can be extended to split the whole column into separate words in rows.

As long as you hide that ugly generated code within a batch script ;-) And if it's a huge varchar you'll hit the "1MB maximum sql size" limit...

     > -- one SQL with product join to get right id's
     > -- it is generic but might not show best performance espacially for
     big
     > varchar fields
     > select t.upi_id,
     >           coalesce(c.id,0) as last_position,
     >           substring(char_field from last_position + 1) as last_field,
     >           char_field
     > from char_test t
     >         left outer join
     >         (select ci.calendar_date-current_date as id
     >          from sys_calendar.calendar ci,
     >                  (select max(characters(char_field)) as max_len from
     > char_test) m
     >          where id between 1 and m.max_len
     >         ) c
     > on index(t.char_field,' ') > 0
     >      and last_position between index(t.char_field,' ') and
     > characters(t.char_field)
     > where (last_position is null
     >   or last_position between index(t.char_field,' ') and
     > characters(t.char_field)
     >   )
     > qualify max(case when substring(char_field from last_position for 1) ='
     > ' then last_position else null end) over (partition by upi_id) =
     > last_position or last_position = 0
     > order by upi_id
     > ;

If you apply a filtering condition early the number of spool rows will be fairly reduced and the qualify will be simpler:

     select
        t.upi_id,
        coalesce(c.id,0) as last_position,
        substring(char_field from last_position + 1) as last_field
     from char_test t
     left outer join
       (select ci.calendar_date-current_date as id
        from sys_calendar.calendar ci,
            (select max(characters(char_field)) as max_len
             from char_test) m
        where id between 1 and m.max_len
       ) c
     on id <= char_length(t.char_field)
     and substring(char_field from id for 1 ) = ' '
     qualify
        max(last_position) over (partition by upi_id) = last_position
     ;

and instead of max a rank might be faster:

     qualify
        rank() over (partition by upi_id order by last_position desc) = 1

Thus you could get the Nth occurence also...

Dieter

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