|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-18 : 08:47:50
|
| Ismail writes "Hi, i need help please, my query returns some null recordsI have multiple selects, tried if exists & not null. here is my query,it looks a bit hectic but if anyone can help.Thank You in Anticipationselect 'Period|Item|Channel|Region|SubBrand|Category|SubBus Unit|Regrade Filter|SalesVolume|SalesValue|BudgetVolume|BudgetValue|HistoryVolume|HistoryValue|ForecastVolume' as OutputVar Union All Select rtrim(Period) +'|'+rtrim(V_Main) +'|'+str(SalesVolume,9,4) +'|'+str(SalesValue,9,4) +'|'+str(BudgetVolume,9,4) +'|'+str(BudgetValue,9,4) +'|'+str(HistoryVolume,9,4) +'|'+str(HistoryValue,9,4) +'|'+str(ForecastVolume,9,4)from ( Select (select coalesce(rtrim(Run_Period),'0.00') from dbo.MIS_System_Parameters where Run_Type = 'SELECTED') as Period ,rtrim(Variable1) as V_Main ,sum(SalesVolume) as SalesVolume ,sum(SalesValue) as SalesValue ,sum(BudgetVolume) as BudgetVolume ,sum(BudgetValue) as BudgetValue ,sum(HistoryVolume) as HistoryVolume ,sum(HistoryValue) as HistoryValue ,sum(ForecastVolume) as ForecastVolume from ( Select coalesce(rtrim(v15.Item_Descr),'') + ' [' + coalesce(rtrim(v15.Item_Code) + ']','') +'|'+case when DSRP_ItemWhsNode_ABC='C' then 'Depot Node' else coalesce(rtrim(v5.Attrib_Code_Descr),'') end +'|'+coalesce(rtrim(v3.Attrib_Code_Descr),'') +'|'+coalesce(rtrim(v6.Attrib_Code_Descr),'') + ' [' + coalesce(rtrim(v4.Item_SubBrand) + ']' ,'') +'|'+coalesce(rtrim(v11.Attrib_Code_Descr),'') +'|'+coalesce(rtrim(v12.Attrib_Code_Descr),'') +'|'+coalesce(rtrim(v15.Item_Regrade),'') as Variable1 ,SalesVolume ,SalesValue ,BudgetVolume ,BudgetValue ,HistoryVolume ,HistoryValue ,ForecastVolume from ( select MCI_DB ,MCI_Cust_Code_Srce ,MCI_CorpItem as Item ,MCI_BusEnt_Sale as BusEntitySales ,MCI_Company ,MCI_Net_Volume as SalesVolume ,MCI_Net_Value_R as SalesValue ,0 as BudgetVolume ,0 as BudgetValue ,0 as HistoryVolume ,0 as HistoryValue ,0 as ForecastVolume from dbo.MIS_MCI_Sales inner join dbo.MIS_System_Parameters as P on P.Run_Type = 'SELECTED' where MCI_Company = '1' and MCI_Period = P.Run_Period Union All select Budget_DB ,Budget_CustomerCode ,Budget_Item ,Cust_Budget_BusEntity ,'1' ,0 ,0 ,(Budget_Volume*P.Run_SplitFactor) ,(Budget_Value*P.Run_SplitFactor) ,0 ,0 ,0 from dbo.MIS_Customer_Item_Budget left join dbo.MIS_Corp_Customer_Master on Budget_DB = Cust_DB and Budget_CustomerCode = Cust_Code_Srce inner join dbo.MIS_System_Parameters as P on P.Run_Type = 'SELECTED' where Budget_Period = P.Run_Period and Cust_Company = '1' Union All select MCI_DB ,MCI_Cust_Code_Srce ,MCI_CorpItem as Item ,MCI_BusEnt_Sale as BusEntity |
|
Exclude Null records from multiple selects 