Archives of the TeradataForum
Message Posted: Tue, 19 Feb 2008 @ 16:24:45 GMT
How about inserting the results of:
select n.product, n.new_Expanded_product from (select product, substr(Expanded_product,1,3) || cast(day_of_calendar - 1as char(1)) new_Expanded_product from prod_table cross join (select * from sys_calendar.calendar where day_of_calendar < 11) cal where product like '%X' ) n left join prod_table on substr(n.product,1,3) = substr(prod_table.product,1,3) and n.new_Expanded_product = prod_table.Expanded_product where prod_table.product is null
The inner query gets all the X rows and joins them to the numbers 0-9 taken from sys_calendar.calendar.day_of_calendar.
But you don't need to insert all of them, just those you don't have. So you outer join that set back to prod_table on the first three characters of product + Expanded_product and take the ones with no match. In your example, from products starting with 111, you already have 0110 and 0111. But suppose you had, say (112X,1123) and (1127,1127) - you'd want to insert 0-2,4-6,8,9.
You can play around with this by substituting the subquery below for prod_table (be sure to alias it as prod_table, though):
(select * from (select '011X' as product, cast('0110' as char(4))as Expanded_product) a union select * from (select '0111' as product, cast('0111' as char(4)) as Expanded_product) b)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|