SQL Queries :Using Computed Columns as Filter

[oyad]

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

[Harsh Gupta]

Use HAVING Clause.

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.

[oyad]

Use HAVING Clause.

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

[Harsh Gupta]

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

HAVING fun_count(x_items) > 0

[Hack]

Moved To Database Development