Problem with group by on view containing case

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)

Problem with group by on view containing case

Author

Topic

nbokare
Starting Member

3 Posts

Posted - 2008-11-04 : 01:34:42

Hi,

This is my view:

-------------------------------------------
CREATE VIEW prices
AS
SELECT
CASE WHEN (E.product_id IS NULL) THEN
D.product_id
ELSE
E.product_id
END AS product_ids,
P.price
FROM
products P
LEFT OUTER JOIN electronic_products E
ON (P.product_id = E.product_id)
LEFT OUTER JOIN electronic_products E
ON (P.product_id = D.product_id)
-------------------------------------------

Now when I execute the following query (instead of the view):

-------------------------------------------
SELECT sum(price) from prices group by product_id
-------------------------------------------

I get the output as required
But when i try to execute the query

-------------------------------------------
SELECT
CASE WHEN (E.product_id IS NULL) THEN
D.product_id
ELSE
E.product_id
END AS product_ids,
sum(P.price)
FROM
products P
LEFT OUTER JOIN electronic_products E
ON (P.product_id = E.product_id)
LEFT OUTER JOIN electronic_products E
ON (P.product_id = D.product_id)
group by product_id
-------------------------------------------

It gives me error that "invalid column name product_ids"
Here i have to group by all the columns that use in the select part of the query, viz. E.product_id and D.product_id. Also it does not accept the alias given to the new column.

But if view is just a query, how does it not give the same error in the first case also?

Regards,
Nik

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 01:50:20

are you sure that view has been refreshed since last table changes.it seems like you had some changes being done to base tables and view has not ben refreshed since.

nbokare
Starting Member

3 Posts

Posted - 2008-11-04 : 02:04:23

The tables have not been changed.

BTW, the tables and views I've given here are just an example. But I can promise that the example is good enough to explain my doubts.

According to my understanding, "View is just a query".

If that's the case, doubt I'm having is if its just a query, both the cases should behave in the same fashion.
But they don't.
The second case gives errors. I know why that is.
But what i don't get is how does the sql server handle this error when I'm querying the view.

Regards,
Nik

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 02:34:19

the error you posted happens only if it cant find the specified column in table. thats why i doubt whether the table has been changed.
Another possibility is that you're using the second query as a part of another big query. If thats true, can you post the whole query? i dont think the select query alone will cause such an error under current scenario.

nbokare
Starting Member

3 Posts

Posted - 2008-11-04 : 02:58:17

It wont find the column name because its an alias. That's why its giving the error.
If i want to run the query without any errors, I can in two ways:

-------------------------------------------
SELECT sum(abc.price) FROM
{
SELECT
CASE WHEN (E.product_id IS NULL) THEN
D.product_id
ELSE
E.product_id
END AS product_ids
FROM
products P
LEFT OUTER JOIN electronic_products E
ON (P.product_id = E.product_id)
LEFT OUTER JOIN electronic_products E
ON (P.product_id = D.product_id)
) as abc
group by abc.product_id
-------------------------------------------

OR

-------------------------------------------
SELECT
CASE WHEN (E.product_id IS NULL) THEN
D.product_id
ELSE
E.product_id
END AS product_ids,
sum(P.price)
FROM
products P
LEFT OUTER JOIN electronic_products E
ON (P.product_id = E.product_id)
LEFT OUTER JOIN electronic_products E
ON (P.product_id = D.product_id)
group by D.product_id, E.product_id
-------------------------------------------

The first case works fine because the nested query part creates an alias for the result set. This hides the alias part of the internal query for the outer query.
So the outer query gets the "product_ids" as a column. (I hope you get what I want to say. :) )

I think, the sql server must be implementing views like the second case here.
Please correct me if I am wrong.

Regards,
Nik

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 03:07:43

yup..thats right. once you create a view its like a virtual table. SO all the columns inside body will have seperate existence (including the alias) as the column of view. thats why it works.
When you run it as query, it wont distinguish alias as a column,unless you form a derived table out of it. Hence it errors.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 04:47:23

You have two tables aliased as E. It will not work.
And why are you joining same table twice with same condition? It makes no sense at all.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources