February 9, 2011 at 12:18 pm
So I'm getting this error:
Column 'maintable.memberid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
But I really can't see the problem with SQL below. This is running on SQL 2005 SP3. What have I missed???
select
m_mainid,
case
when memberid in ('A','B','C',) then 2000
when memberid = 'E' and m_mainid in (select id from temptable) then 1000
else memberid end,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield,
max(isnull(ch_origamtfield,0))
from maintable
left outer join changetable on m_mainid=ch_m_mainid
and datediff(m,convert(datetime, field3 + '-1'),ch_date) between 0 and 3
group by
m_mainid,
case
when memberid in ('A','B','C',) then 2000
when memberid = 'E' and m_mainid in (select id from temptable) then 1000
else memberid end,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield
go
February 9, 2011 at 1:14 pm
there are a couple of things wrong with your code. The first is when you are checking for the memberid in A,B,C you have an extra comma after C. Also you can't use that clause mainId in (select id from temptable) in the group by clause. Try to put the max and group by outside of the original select. Try this:
declare @maintable table(m_mainid int, memberID varchar(20), corpid int, field2 int, field3 varchar(20),
orig_startdt smalldatetime, orig_amtfield int, startdt smalldatetime, amtfield int)
declare @changetable table(ch_m_mainid int, ch_date smalldatetime, ch_origamtfield int )
declare @temptable table (id int)
select m_mainid,
memberid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield,
max(ch_origamtfield) ch_origamtfield
from (
select
m_mainid,
case
when memberid in ('A','B','C') then 2000
when memberid = 'E' and m_mainid in (select id from @temptable) then 1000
else memberid end memberid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield,
isnull(ch_origamtfield,0) ch_origamtfield
from @maintable
left outer join @changetable on m_mainid=ch_m_mainid
and datediff(m,convert(datetime, field3 + '-1'),ch_date) between 0 and 3) v
group by
m_mainid,
memberid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 1:19 pm
I think the 'case' in the 'group by' is confusing things. What happens with:
select
m_mainid,
case
when memberid in ('A','B','C',) then 2000
when memberid = 'E' and m_mainid in (select id from temptable) then 1000
else memberid end as 'memid',
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield,
max(isnull(ch_origamtfield,0))
from maintable
left outer join changetable on m_mainid=ch_m_mainid
and datediff(m,convert(datetime, field3 + '-1'),ch_date) between 0 and 3
group by
m_mainid,
memid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield
go
February 9, 2011 at 1:36 pm
dave,
I think your solution will skew the results since a memberid of A, B, and C will be split out into 3 rows even though the OP has substituted them for 2000. Also, the case statement wii generate an error if the memberid is a letter other than ABC or E. Here's some sample data and both solutions to see what I am talking about. Mainid 1 has memberid A, B and C. The first solution combines them, the second one has them split out. Maybe the OP can tell us which one he is expecting for a result
declare @maintable table(m_mainid int, memberID varchar(20), corpid int, field2 int, field3 varchar(20),
orig_startdt smalldatetime, orig_amtfield int, startdt smalldatetime, amtfield int)
declare @changetable table(ch_m_mainid int, ch_date smalldatetime, ch_origamtfield int )
declare @temptable table (id int)
insert into @maintable
select 1, 'A', 1,1,'2011-01', '2011-01-05', 1, '2011-01-04', 5 union all
select 1, 'B', 1,1,'2011-01', '2011-01-05', 1, '2011-01-04', 5 union all
select 1, 'C', 1,1,'2011-01', '2011-01-05', 1, '2011-01-04', 5 union all
select 1, 'E', 1,1,'2011-01', '2011-01-05', 1, '2011-01-04', 5 union all
select 3, 'E', 1,1,'2011-01', '2011-01-05', 1, '2011-01-04', 5 union all
select 2, 'E', 1,1,'2011-01', '2011-01-05', 1, '2011-01-04', 5
insert into @temptable select 2
insert into @changetable select 3, '2011-01-09', 37
select m_mainid,
memberid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield,
max(ch_origamtfield) ch_origamtfield
from (
select
m_mainid,
case
when memberid in ('A','B','C') then '2000'
when memberid = 'E' and m_mainid in (select id from @temptable) then '1000'
else memberid end memberid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield,
isnull(ch_origamtfield,0) ch_origamtfield
from @maintable
left outer join @changetable on m_mainid=ch_m_mainid
and datediff(m,convert(datetime, field3 + '-1'),ch_date) between 0 and 3) v
group by
m_mainid,
memberid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield
select
m_mainid,
case
when memberid in ('A','B','C') then '2000'
when memberid = 'E' and m_mainid in (select id from @temptable) then '1000'
else memberid end as 'memid',
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield,
max(isnull(ch_origamtfield,0))
from @maintable
left outer join @changetable on m_mainid=ch_m_mainid
and datediff(m,convert(datetime, field3 + '-1'),ch_date) between 0 and 3
group by
m_mainid,
memberid,
corpid,
field2,
field3,
orig_startdt,
orig_amtfield,
startdt,
amtfield
go
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply