InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 16:01:38 GMT20http://www.sqlservercentral.com/Forums/Topic1382323-392-1.aspxThe first thing that stands out is why do you have all the NOLOCK hints? You are aware of the problems that this hint causes?Second you need to treat each SELECT in a UNION as its own grouping setSo you would group up the first select, union, group the second select[code="sql"]SELECT Col1, Col2,FROM Tab1GROUP BY Col1UNIONSELECT Col1, Col2FROM Tab2GROUP BY Col1[/code]Thu, 08 Nov 2012 02:08:57 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1382323-392-1.aspxdeclare @CompanyName Varchar (20)set @CompanyName = '2-G9BF'SELECT PolicyID, Client_Full_Name INTO #Stage1FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)WHERE IFA_Company_ID = @CompanyNameAND Master_Client = 'Y'SELECT PolicyID, Client_Full_Name INTO #Stage2FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)WHERE IFA_Company_ID = @CompanyNameAND Master_Client = 'N'-- get the maximum investment holding per policy into a temp tableSELECT IH.policy_id, MAX(IH.inv_holding) as [max_INV_Holding]INTO #MaxInvestHoldingPerPolicyFROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_Sold_Prod SP WITH (NOLOCK)JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_HOLDING IH WITH (NOLOCK)ON IH.policy_id = SP.policy_id AND IH.product_code = SP.product_code AND IH.instance = SP.instanceJOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)ON FPB.policy_id = IH.POLICY_ID AND FPB.product_code = IH.product_code AND FPB.instance = IH.instanceWHERE FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'GROUP BY IH.policy_idSELECT t1.PolicyID, t1.Client_Full_Name, ISNULL (t2.Client_Full_Name,'') SecondInvestorINTO #Stage3FROM #Stage1 t1LEFT JOIN #Stage2 t2ON t1.PolicyId = t2.PolicyIDORDER BY t1.PolicyIDDROP TABLE #Stage1DROP TABLE #Stage2-- Get the first fieldsSELECT pwh.IFA_Company_Name as IFA_Company_Name,t3.Client_Full_Name as Client_Full_Name,t3.SecondInvestor as SecondInvestor,IB.POLICY_ID AS Policy,CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_ValueFROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_IH_BASIS IB WITH (NOLOCK)LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.pr_ih_fund_spd FS WITH (NOLOCK)ON FS.POLICY_ID = IB.POLICY_ID AND FS.INV_HOLDING = IB.INV_HOLDINGLEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.s_asset SA WITH (NOLOCK)ON SA.ASSET_NUM = IB.POLICY_ID LEFT Join SELESTIA_BI_STAGING_SOURCE.dbo.pr_inv_ptf IP WITH (NOLOCK)ON IP.INV_PTF_NAME = FS.INV_PTF_NAMELEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)ON FP.INV_PTF_NAME = IP.INV_PTF_ALIASAND EFFECTIVE_DATE = (SELECT MAX (EFFECTIVE_DATE) FROM DWH..FACT_FUNDPRICES WITH (NOLOCK))LEFT JOIN DWH.dbo.dim_policywhoswho PWH WITH (NOLOCK)ON PWH.POLICYID = IB.POLICY_IDLEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_SOLD_PROD SP WITH (NOLOCK)ON SP.POLICY_ID = IB.POLICY_ID AND SP.PRODUCT_CODE = IB.PRODUCT_CODE AND SP.INSTANCE = IB.INSTANCELEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_INV_Holding IH WITH (NOLOCK)ON IH.POLICY_ID = IB.POLICY_ID AND IH.PRODUCT_CODE = IB.PRODUCT_CODE AND IH.INSTANCE = IB.INSTANCE AND IH.INV_HOLDING = IB.INV_HOLDINGLEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)ON FPB.POLICY_ID = IH.POLICY_ID AND FPB.PRODUCT_CODE = IH.PRODUCT_CODE AND FPB.INSTANCE = IH.INSTANCE AND FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_SPLIT_FEES SF WITH (NOLOCK)ON SF.POLICY_ID = IB.POLICY_IDAND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPEAND SF.IBU_COMM_CLASS = IB.IBU_COMM_CLASSLEFT JOIN #MaxInvestHoldingPerPolicy MH WITH (NOLOCK)ON MH.POLICY_ID = SP.POLICY_ID-- get the ServicingContact (amended for Flare 450396)LEFT JOIN (SELECT a.INTEGRATION_ID , a.FST_NAME + ' ' + a.LAST_NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]FROM Selestia_BI_Staging_Source..S_CONTACT a WITH (NOLOCK)LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_SER_CON_ID = b.ROW_IDWHERE a.X_INVSTR_FLG = 'Y'UNIONSELECT a.INTEGRATION_ID , a.NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]FROM Selestia_BI_Staging_Source..S_ORG_EXT a WITH (NOLOCK)LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_PR_SER_PER_ID = b.ROW_ID WHERE a.INVSTR_FLG = 'Y') UNON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULTLEFT JOIN #Stage3 t3ON pwh.PolicyID = t3.PolicyIDWHERE fs.fund_code = 'FV'AND FS.Value >= 1 AND SA.owner_accnt_id = @CompanyNameAND pwH.Master_Client = 'Y'AND pwH.status IN ('VERI','INNF')AND SP.IFA_REMUNE_TYPE = 'COMMISSION'---then get the second fields UNION SELECT pwh.IFA_Company_Name as IFA_Company_Name,t3.Client_Full_Name as Client_Full_Name,t3.SecondInvestor as SecondInvestor,PIH.POLICY_ID AS Policy,CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_ValueFROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_HOLDING PIH WITH (NOLOCK)LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_IH_FUND_SPD FS WITH (NOLOCK)ON PIH.POLICY_ID = FS.POLICY_ID AND PIH.INV_HOLDING = FS.INV_HOLDING LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.S_ASSET SA WITH (NOLOCK)ON PIH.POLICY_ID = SA.ASSET_NUMLEFT Join SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_PTF IP WITH (NOLOCK)ON FS.INV_PTF_NAME = IP.INV_PTF_NAMELEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)ON IP.INV_PTF_ALIAS = FP.INV_PTF_NAMEAND EFFECTIVE_DATE = (SELECT MAX (EFFECTIVE_DATE) FROM DWH..FACT_FUNDPRICES WITH (NOLOCK))LEFT JOIN DWH.dbo.DIM_POLICYWHOSWHO PWH WITH (NOLOCK)ON PIH.POLICY_ID = PWH.POLICYIDLEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_SOLD_PROD SP WITH (NOLOCK)ON PIH.POLICY_ID = SP.POLICY_ID AND PIH.PRODUCT_CODE = SP.PRODUCT_CODE AND PIH.INSTANCE = SP.INSTANCE LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_IH_BASIS IB WITH (NOLOCK)ON PIH.POLICY_ID = IB.POLICY_ID AND PIH.INV_HOLDING = IB.INV_HOLDINGAND PIH.PRODUCT_CODE = IB.PRODUCT_CODE AND PIH.INSTANCE = IB.INSTANCE LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)ON PIH.POLICY_ID = FPB.policy_id AND PIH.product_code = FPB.product_code AND PIH.instance = FPB.instance AND FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_SPLIT_FEES SF WITH (NOLOCK)ON PIH.POLICY_ID = SF.POLICY_IDAND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPE--AND SF.IBU_COMM_CLASS = PIH.IBU_COMM_CLASSLEFT JOIN #MaxInvestHoldingPerPolicy MH WITH (NOLOCK)ON MH.POLICY_ID = SP.POLICY_ID-- get the ServicingContact (amended for Flare 450396)LEFT JOIN (SELECT a.INTEGRATION_ID , a.FST_NAME + ' ' + a.LAST_NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]FROM Selestia_BI_Staging_Source..S_CONTACT a WITH (NOLOCK)LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_SER_CON_ID = b.ROW_IDWHERE a.X_INVSTR_FLG = 'Y'UNIONSELECT a.INTEGRATION_ID , a.NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]FROM Selestia_BI_Staging_Source..S_ORG_EXT a WITH (NOLOCK)LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_PR_SER_PER_ID = b.ROW_ID WHERE a.INVSTR_FLG = 'Y') UNON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULTLEFT JOIN #Stage3 t3ON pwh.PolicyID = t3.PolicyIDWHERE fs.fund_code = 'FV'AND FS.Value >= 1 AND SA.owner_accnt_id = @CompanyNameAND pwH.Master_Client = 'Y'AND pwH.status IN ('VERI','INNF')AND SP.IFA_REMUNE_TYPE = 'FEES'GROUP BY pwh.IFA_Company_Name, t3.Client_Full_Name, t3.SecondInvestor, IB.POLICY_ID, PIH.POLICY_ID, FS.VALUE, FP.FUND_PRICEORDER BY IB.POLICY_IDDROP TABLE #Stage3DROP TABLE #MaxInvestHoldingPerPolicyThu, 08 Nov 2012 02:00:03 GMThoseamhttp://www.sqlservercentral.com/Forums/Topic1382323-392-1.aspxPlease post the full query.Thu, 08 Nov 2012 01:51:02 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1382323-392-1.aspxHi I have this code:SELECT pwh.IFA_Company_Name as IFA_Company_Name,t3.Client_Full_Name as Client_Full_Name,t3.SecondInvestor as SecondInvestor,IB.POLICY_ID AS Policy,CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_ValueUNIONSELECT pwh.IFA_Company_Name as IFA_Company_Name,t3.Client_Full_Name as Client_Full_Name,t3.SecondInvestor as SecondInvestor,PIH.POLICY_ID AS Policy,CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Valueand after my joins I have:GROUP BY pwh.IFA_Company_Name, t3.Client_Full_Name, t3.SecondInvestor, IB.POLICY_ID, PIH.POLICY_ID, FS.VALUE, FP.FUND_PRICEORDER BY IB.POLICY_IDand I get this error:Msg 8120, Level 16, State 1, Line 45Column 'DWH.dbo.dim_policywhoswho.IFA_Company_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Msg 104, Level 16, State 1, Line 45ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.Please help.Thu, 08 Nov 2012 01:47:03 GMThoseam