i unable insert values using query. data fetched , created correctly. having issues "insert table part"
i fetching data set of functions , joins , trying insert result table temp_parentg_sales_total.
while data getting fetched correctly, insert part having problems with. following query using:
insert mdm_stat.dbo.temp_parentg_sales_totals (parent_description, total_sales, ranking) select abalph, total, ranking from( --- query part bottom working correctly. use mdm_stat declare @runmonth int; declare @runyear int; declare @period int; declare @fiscalrunyear int; declare @fiscalrunyear_begin int; declare @ranking_type varchar(100); declare @ranking_use varchar(100); declare @aupper decimal(12,2); declare @alower decimal(12,2); declare @bupper decimal(12,2); declare @blower decimal(12,2); declare @cupper decimal(12,2); declare @clower decimal(12,2); declare @dupper decimal(12,2); declare @dlower decimal(12,2); set @ranking_type = 'customer sales'; set @ranking_use='customer sales ranking 8_4_2016'; set @alower =(select lower_limit dbo.xref_abc_ranking_rules ranks='a'and ranking_type= @ranking_type , ranking_use = @ranking_use); set @bupper =(select upper_limit dbo.xref_abc_ranking_rules ranks='b'and ranking_type= @ranking_type , ranking_use = @ranking_use); set @blower =(select lower_limit dbo.xref_abc_ranking_rules ranks='b'and ranking_type= @ranking_type , ranking_use = @ranking_use); set @cupper =(select upper_limit dbo.xref_abc_ranking_rules ranks='c'and ranking_type= @ranking_type , ranking_use = @ranking_use); set @clower =(select lower_limit dbo.xref_abc_ranking_rules ranks='c'and ranking_type= @ranking_type , ranking_use = @ranking_use); set @dupper =(select upper_limit dbo.xref_abc_ranking_rules ranks='d'and ranking_type= @ranking_type , ranking_use = @ranking_use); set @runmonth=month(getdate()); set @runyear=year(getdate()); set @period= case when @runmonth>3 (@runmonth-3) else 9+@runmonth end ; set @fiscalrunyear= case when @runmonth>3 @runyear else @runyear-1 end ; set @fiscalrunyear_begin= case when @period=12 @fiscalrunyear else @fiscalrunyear-1 end ; select f.abalph,f.total, ranking= case when f.total >@alower 'a' when f.total >@blower , f.total<=@bupper 'b' when f.total >@clower , f.total<=@cupper 'c' else 'd' end ( select ab.abalph, c.total jde_test.testdta.f0101 ab right join ( select sum(b.total) total,g.mapa8 jde_test.testdta.f0150 g left join ( --determining 12 months sale per customer select a.customer, sum( a.sales) total ( select s.customer, s.salesyear, s.sales, s.period ( select distinct customer,salesyear,sum(cast(dollar01 decimal(11,2))) sales, '1' period sds.smargintext group customer,salesyear union select distinct customer,salesyear,sum(cast(dollar02 decimal(11,2))) sales, '2' period sds.smargintext group customer,salesyear ) s s.salesyear=@fiscalrunyear , s.period<=@period union select t.customer, t.salesyear, t.sales, t.period ( select distinct customer,salesyear,sum(cast(dollar01 decimal(11,2))) sales, '1' period sds.smargintext group customer,salesyear union select distinct customer,salesyear,sum(cast(dollar02 decimal(11,2))) sales, '2' period sds.smargintext group customer,salesyear ) t t.salesyear=@fiscalrunyear_begin , t.period>@period ) group a.customer ) b on cast(b.customer int) = g.maan8 b.customer <> 'unspec' group g.mapa8 ) c on c.mapa8 = ab.aban8 ) f order f.total asc)
this happens because trying enclose script inside insert statement.
you have add insert statement right @ top select statement gives necessary columns insert table.
i guess below top query.
insert mdm_stat.dbo.temp_parentg_sales_totals (parent_description, total_sales, ranking) select f.abalph,f.total, ranking= case when f.total >@alower 'a' when f.total >@blower , f.total<=@bupper 'b' when f.total >@clower , f.total<=@cupper 'c' else 'd' --suppressed other part of query b.customer <> 'unspec' group g.mapa8 ) c on c.mapa8 = ab.aban8 ) f order f.total asc 
Comments
Post a Comment