SQL Queries :Using Computed Columns as Filter

am trying to use a computed column in filter clause of an SQL query but its giving me errors, i guess its either not possible or am not doing it correctly. Any SQL guru 4 help?
(just a sample below)

select DisTable.*,s_name,fun_count(x_items) as myItems
from DisTable,UserTable
where DisTable.Name=s_name
and myItems > 0

Use HAVING Clause.

sql Code:

select DisTable.*,s_name,fun_count(x_items) as myItems
from DisTable,UserTable
where DisTable.Name=s_name
GROUP BY DisTable.*,s_name --instead of *, you need to mention each column separately
HAVING myItems > 0

The only glitch is if you have way too many columns, adding them to Group by clause could be cumbersome.

If fun_count() is user-defined function, another (possible) way could be that you can let it return records based on the conditions you want to put here. E.g. In your example, let it send you all records which will be greater than 0. Just a guess though. I don't know how this function looks like.

Quote:

---

Originally Posted by Harsh Gupta

Use HAVING Clause.

sql Code:

select DisTable.*,s_name,fun_count(x_items) as myItems
from DisTable,UserTable
where DisTable.Name=s_name
GROUP BY DisTable.*,s_name --instead of *, you need to mention each column separately
HAVING myItems > 0

The only glitch is if you have way too many columns, adding them to Group by clause could be cumbersome.

If fun_count() is user-defined function, another (possible) way could be that you can let it return records based on the conditions you want to put here. E.g. In your example, let it send you all records which will be greater than 0. Just a guess though. I don't know how this function looks like.

---

from your sample, i get error on line 5, myItems is an invalid identifier

Sorry for the typo. You cannot use alias names. It should be this:

Code:

---

HAVING fun_count(x_items) > 0

---

Moved To Database Development