November 8, 2012 at 1:47 am
Hi
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 ASPolicy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value
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 ASPolicy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value
and 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_PRICE
ORDER BY IB.POLICY_ID
and I get this error:
Msg 8120, Level 16, State 1, Line 45
Column '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 45
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.Please help.
November 8, 2012 at 1:51 am
Please post the full query.
November 8, 2012 at 2:00 am
declare @CompanyName Varchar (20)
set @CompanyName = '2-G9BF'
SELECT PolicyID, Client_Full_Name
INTO #Stage1
FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)
WHERE IFA_Company_ID = @CompanyName
AND Master_Client = 'Y'
SELECT PolicyID, Client_Full_Name
INTO #Stage2
FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)
WHERE IFA_Company_ID = @CompanyName
AND Master_Client = 'N'
-- get the maximum investment holding per policy into a temp table
SELECT IH.policy_id, MAX(IH.inv_holding) as [max_INV_Holding]
INTO #MaxInvestHoldingPerPolicy
FROM 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.instance
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
WHERE FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'
GROUP BY IH.policy_id
SELECT
t1.PolicyID
,t1.Client_Full_Name
,ISNULL (t2.Client_Full_Name,'')SecondInvestor
INTO #Stage3
FROM #Stage1t1
LEFT JOIN #Stage2t2
ON t1.PolicyId = t2.PolicyID
ORDER BY t1.PolicyID
DROP TABLE #Stage1
DROP TABLE #Stage2
-- Get the first fields
SELECT
pwh.IFA_Company_Name as IFA_Company_Name,
t3.Client_Full_Name as Client_Full_Name,
t3.SecondInvestor as SecondInvestor,
IB.POLICY_ID ASPolicy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value
FROM 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_HOLDING
LEFT 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_NAME
LEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)
ON FP.INV_PTF_NAME = IP.INV_PTF_ALIAS
AND 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_ID
LEFT 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.INSTANCE
LEFT 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_HOLDING
LEFT 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_ID
AND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPE
AND SF.IBU_COMM_CLASS = IB.IBU_COMM_CLASS
LEFT 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_ID
WHERE a.X_INVSTR_FLG = 'Y'
UNION
SELECT 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'
) UN
ON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULT
LEFT JOIN #Stage3t3
ON pwh.PolicyID = t3.PolicyID
WHERE fs.fund_code = 'FV'
AND FS.Value >= 1
AND SA.owner_accnt_id = @CompanyName
AND 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 ASPolicy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value
FROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_HOLDINGPIH 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_NUM
LEFT Join SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_PTF IP WITH (NOLOCK)
ON FS.INV_PTF_NAME = IP.INV_PTF_NAME
LEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)
ON IP.INV_PTF_ALIAS = FP.INV_PTF_NAME
AND 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.POLICYID
LEFT 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_HOLDING
AND 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_ID
AND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPE
--AND SF.IBU_COMM_CLASS = PIH.IBU_COMM_CLASS
LEFT 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_ID
WHERE a.X_INVSTR_FLG = 'Y'
UNION
SELECT 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'
) UN
ON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULT
LEFT JOIN #Stage3t3
ON pwh.PolicyID = t3.PolicyID
WHERE fs.fund_code = 'FV'
AND FS.Value >= 1
AND SA.owner_accnt_id = @CompanyName
AND 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_PRICE
ORDER BY IB.POLICY_ID
DROP TABLE #Stage3
DROP TABLE #MaxInvestHoldingPerPolicy
November 8, 2012 at 2:08 am
The 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 set
So you would group up the first select, union, group the second select
SELECT
Col1,
Col2,
FROM
Tab1
GROUP BY
Col1
UNION
SELECT
Col1,
Col2
FROM
Tab2
GROUP BY
Col1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply