-
Need help with a complex sql select
Hey, I am trying to do a pretty complex sql select with a few unions. The select works in oracle just fine but when i try and use it on SQLServer2005 it blows up. It is just giving me a syntax error near 'group' but if you need information on what i need to do with it I'll post that later.
The sql inside the parentheses work fine by itself but when you add in the surrounding text it blows up.
Any help would be appreciated and thank you in advance.
select Prod, Loc, sum(errors) Errors, sum(warnings) Warnings, sum(infos) Infos
from
(
select f_product Prod, f_location Loc, count(*) errors, 0 warnings, 0 infos
from f_messages
where f_project = 90
and f_mesg_severity = 'E'
group by f_product, f_location
union all
select f_product, f_location, 0, count(*), 0
from f_messages
where f_project = 90
and f_mesg_severity = 'W'
group by f_product, fcst_location
union all
select f_product, f_location, 0, 0, count(*)
from f_messages
where f_project = 90
and f_mesg_severity = 'I'
group by f_product, f_location
)
group by Prod, Loc
-
Got any error message? On which rdbms?
-
The rbdm is sql server 2005 and the error its giving me is
"Incorrect syntax near the keyword 'group'."
-
I believe sql server doesn't like 'group by' in union.
-
You need to give your sub select statement an alias, like this:
select Prod, Loc, sum(errors) Errors, sum(warnings) Warnings, sum(infos) Infos
from
(
select f_product Prod, f_location Loc, count(*) errors, 0 warnings, 0 infos
from f_messages
where f_project = 90
and f_mesg_severity = 'E'
group by f_product, f_location
union all
select f_product, f_location, 0, count(*), 0
from f_messages
where f_project = 90
and f_mesg_severity = 'W'
group by f_product, fcst_location
union all
select f_product, f_location, 0, 0, count(*)
from f_messages
where f_project = 90
and f_mesg_severity = 'I'
group by f_product, f_location
) AS X
group by Prod, Loc
-
thank you so much for the help, fixed it perfectly
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|