Morning all,
So as the title suggests, I have a query (below) which should from all my tired eyes can see, work.
However the error message i keep recieving is:
Column 'Medicala6744.patid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Now before you ask, i have indeed tried as many combinations of GROUP BY, Distinct, Union, Union all etc.
However i haven't found the answer to it yet...
The query itself is as follows
Code:
/* Index date = date of first diagnosis of AF (cases) Controls will have the same
index date as its matched case patient */
Select * from
(
select distinct m.patid, m.medcode,MIN(m.eventdate)as index_date
from Medicala6744 as m
inner join TargetPat as a
on a.patid=m.patid
where '20060501'<= m.eventdate and'20071130'>= m.eventdate
Union all
select distinct m.patid, m.medcode,MIN(m.eventdate)as index_date
from Ahda6744 as m
inner join TargetPat as a
on a.patid=m.patid
where '20060501'<= m.eventdate and'20071130'>= m.eventdate
) z
inner join AF as f on f.Field1=z.medcode
Group by z.patid, z.medcode, z.index_date
The code is supposed to:
create an index_date field which is the date of the first eventdate medcode that matches AF.
As far as im aware this is the best way to make this work...
but obviously it doesn't
If you have any questions, please ask.
Many thanks
Rixxe