Archives of the TeradataForum

Message Posted: Sun, 25 Sep 2005 @ 19:50:44 GMT

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

Hi,

two SQL's which are doing the job - one direct with difficult performance on my demo version (7 minutes) - it's more an academic solution - and one with is generated with a case statement which comes back within some seconds.

Sorry for the bad SQL formating but it's late here ;-).

     -- generate test data
     create table char_test
     as
     (select calendar_date - current_date as upi_id,
                 trim(trailing from
                        substring('ksdfh asdfa asdfj as df asdfj asdfjha
     sdfgadhugadf vadf vad vaxdfgv yxc b sydfb  adfbv adf a fdv andfav
     sudfhasdv aasdh fasdf hadsf hasgf asdjfhad fgahdfgh asdasdfj
     sdjfadsfjajdfgv ajds gfasdfj khasbdfjhajsdf adfj ahsdjkf asdhj
     jasdfasdhf asdf asd gfa dgf adf ga dfg ad fgadfg  adfg adfg adfg a gf
     ladfg alfaghnjdghjadf g ds afj adfgadf g adfg ad fgladfglad flgaldgn adg
     laldg adlg  ladlg aldg ladlfg adlf gadfg ad fgadlfg adgf lad lg ladl gl
     adg ladgl ladrgl lad lg ldad gl lad g lad lg  algl adlf gadfjl
     gladljfkgnjadg g jajdlf gadjf gahndf gadjgn ldfg'
                                 from random(1,500)
                                 for random(10,256)
                                )
                        ) as char_field
     from sys_calendar.calendar
     where upi_id between 1 and 2000
     ) with data
     unique primary index (upi_id);


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

     --generate sql with case
     --generated sql should show better performance
     select sql_text
     from
     (
     select 99999999 as order_id,
       cast('select upi_id,
     case' as varchar(10000) )as sql_text
     from sys_calendar.calendar
     where calendar_date = current_date
     union all
     select (c.calendar_date - current_date) as order_id,
                ' when characters(char_field) > ' !! order_id !! ' and
     substring(char_field from ' !! order_id !! ' for 1) = '' '' then ' !!
     order_id as sql_text
     from (select max(characters(char_field)) as max_len from char_test) as
     t,
             sys_calendar.calendar c
     where order_id between 1 and max_len
     union all
     select 0 as order_id,
     '
                 else 0
                end as last_position,
                substring(char_field from last_position + 1) as last_field,
                char_field
     from char_test
     order by upi_id;' as sql_text
     from sys_calendar.calendar
     where calendar_date = current_date
     ) as tmp
     order by order_id desc;

     -- paste SQL and execute here

     -- drop example table
     drop table char_test;

Kind regards

Ulrich

--
Ulrich Arndt
www.data2knowledge.de

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