Archives of the TeradataForum

Message Posted: Tue, 19 Feb 2008 @ 16:24:45 GMT

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

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)

Dave

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