Haven't asked it here... figure it wasn't really the right place... but just in case...
I'm working in SQL 2005 and have the following code:
_______________________________
select
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
dt_AGDnum.AGDnum
from
dbo.EXTRACTIONS hdr
join dbo.extdata dta on hdr.extcode = dta.extcode
join dbo.ACTIVED prd on dta.MNHSCODE = prd.NHSCODE
join (select ext.extcode, sum(dta.QTY) as AGDnum from extdata ext join actived prd on
dta.MNHSCODE = prd.NHSCODE where prd.agdn = '1') as dt_AGDnum where
ext.extcode = hdr.extcode
where
hdr.extcode = 'CHACEL_P40105_20090101_20090131_20090224_SC_0020028W'
group by
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
dt_AGDnum.AGDnum
______________________________
But I'm getting this error:
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'where'.
I USED to have this code:
_________________________________
select
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
case prd.AGDN when '1' then sum(dta.QTY) else 0 end) as AGDdnum
from
dbo.EXTRACTIONS hdr
join dbo.extdata dta on hdr.extcode = dta.extcode
join dbo.ACTIVED prd on dta.MNHSCODE = prd.NHSCODE
where
hdr.extcode = 'CHACEL_P40105_20090101_20090131_20090224_SC_00200 28W'
group by
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
prd.AGDN
_________________________________
It does return the correct result, but it returns 2 lines of results because I have to include the 'prd.AGDN' in the group by (AGDN can be 1 or 0).
So... much time spent with google and I attempted the derived table version... but I have failed somewhere and I have no clue what's causing the Msg 156 error.
And SQL folks here know what I'm doing wrong??