| Author |
 Topic  |
|
|
stahorse
Starting Member
42 Posts |
 Posted - 11/08/2012 : 03:45:44
|
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 AS Policy,
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 AS Policy,
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. |
Edited by - stahorse on 11/08/2012 03:57:12
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/08/2012 : 04:15:42
|
While using SET operators you must have to specify GROUP BY clauses separately for each SELECT statement....
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_Value
FROM ...............
GROUP BY
pwh.IFA_Company_Name
, t3.Client_Full_Name
, t3.SecondInvestor
, IB.POLICY_ID
, PIH.POLICY_ID
, FS.VALUE
, FP.FUND_PRICE
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_Value
FROM ...............
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
--
Chandu |
 |
|
|
stahorse
Starting Member
42 Posts |
Posted - 11/08/2012 : 04:37:14
|
| Thank you |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/08/2012 : 04:52:19
|
quote:
Originally posted by stahorse
Thank you
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
GROUP BY Error
