I find it annoying that you can't use aliases in where statements

[nikos101]

I find it annoying that you can't use aliases in where statements

for example:

SELECT TOP 1
CAST(amount
/ ( CASE WHEN ISNULL(rate, 0) <> 0 THEN rate
ELSE 1
END ) AS NUMERIC(10, 2)) AS num
FROM table
WHERE num > 1
ORDER BY num

Are there any workarounds?

[JR__Bob__Dobbs-qSBHQ2]

See attached code for MS SQL 2005 workaround using the sample Northwind database. It's not pretty and I'd test for performance before using something like this in production. If someone knows a better way to do this please post to this thread.

[Newsgroup_User]

> I find it annoying that you can't use aliases in where statements

This isn't really (or... "at all") a CF question. You're better off asking
this sort of thing on a SQL Server forum.

You could use a derived table, I guess:

select top 1 num
from (
SELECT
CAST(amount
/ ( CASE WHEN ISNULL(rate, 0) <> 0 THEN
rate
ELSE 1
END ) AS NUMERIC(10, 2)) AS num
FROM table
)
where num > 1
order by num

(that might not preserve the intent of you SQL (sorry: it's late, I'm
tired), but you get the idea.

--
Adam