Archives of the TeradataForum
Message Posted: Sun, 25 Sep 2005 @ 19:50:44 GMT
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;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|