Derived tables... or fix my case statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Derived tables... or fix my case statement?

Author

Topic

cmhoz
Starting Member

4 Posts

Posted - 2009-03-11 : 21:19:27

Hi all ... I have a query that looks like this:

_________________________________
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).

Google tells me I should be moving this case statement into a Dervied table - but my attempts to do this have failed.

Can anyone help me to get this query to return a 1 line result??

(ps - I have 6 additional case statements to add once I get this right).

Thanks!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 21:34:53

Try this...

select
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
SUM(case when prd.AGDN = '1' then 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

cmhoz
Starting Member

4 Posts

Posted - 2009-03-11 : 22:07:43

That gives me this error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

cmhoz
Starting Member

4 Posts

Posted - 2009-03-12 : 00:18:15

Okay... so google and I have been bonding over 'derived tables' and here's what I've come up with:

_______________________________
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'.

Any idea what I've done wrong??

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-12 : 00:49:12

quote:
Originally posted by cmhoz

Okay... so google and I have been bonding over 'derived tables' and here's what I've come up with:

_______________________________
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~~on dt_AGDnum .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'.

Any idea what I've done wrong??

cmhoz
Starting Member

4 Posts

Posted - 2009-03-12 : 01:27:17

Genius!

I could have stared at that for hours and not seen it...

Thanks!!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-12 : 06:49:15

quote:
Originally posted by cmhoz

Genius!

I could have stared at that for hours and not seen it...

Thanks!!

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources