SELECT col1, col2, dbo.myFunc(@param1, @param2, col1, col2) FROM theTable
How do I add a WHERE here to the result of the function dbo.myFunc, like WHERE resultOfMyFunc > 10 for example?
-
You'll get more hits typically by tagging your question with the specific RDBMS you're using.Dathan– Dathan2009-12-15 19:22:12 +00:00Commented Dec 15, 2009 at 19:22
Add a comment
|
6 Answers
Just use the exact function call you use in the select in the query - the query optimizer should cache the value and use it in both places without re-evaluating the function (this is true for MSSQL, at least).
E.g.,
SELECT col1, col2, myFunc(@arg1, @arg2, col1, col2)
FROM myTable
WHERE myFunc(@arg1, @arg2, col1, col2) > 10
Comments
Just to add to previous answers to cover another option...
If you don't want to repeat the function call in the WHERE clause (e.g. it takes a number of parameters and makes the query less readable in your eyes), you can do:
SELECT * FROM
(
SELECT col1, col2, dbo.myFunc(@param1, @param2, col1, col2) As funcResult
FROM theTable
) x
WHERE x.funcResult > 10
Comments
Try
SELECT
col1,
col2,
dbo.myFunc(@param1, @param2, col1, col2) AS result
FROM
theTable
WHERE
result > 10;
Best wishes,
Fabian
1 Comment
Alon Amir
Doesn't, but I wish that would work, it looks way better than repeating the function and all.
select col1, col2, dbo.myFunc(@param1, @param2, col1, col2)
from theTable
where dbo.myFunc(@param1, @param2, col1, col2) > 10
or (not sure if this second one will work across all SQL versions)
select col1, col2, dbo.myFunc(@param1, @param2, col1, col2) as funcVal
from theTable
where funcVal > 10
2 Comments
Dathan
Yeah, that's been a source of continual frustration for me in using MSSQL. I keep hoping they'll allow column aliases to be used in WHERE expressions, but alas, not yet (maybe this works in 2008? I'm still on 2005).
Tom H
I just did a simple test on 2008 and it looks like you cannot use column aliases in the WHERE clause.
For the love of god. HAVING select col1, col2, dbo.myFunc(@param1, @param2, col1, col2) from theTable where dbo.myFunc(@param1, @param2, col1, col2) > 10
select col1, col2, dbo.myFunc(@param1, @param2, col1, col2) as calculated
FROM theTable
HAVING calculated > 10
3 Comments
Alon Amir
Couldn't get it to work that way, could you please try to rewrite my function that way?
Alon Amir
Still, doesn't work, it gives me the "Invailed column" for the column i did an AS for the alias.
mhughes
not sure which sql flavour youre using. conditions on aggregate function results are usually on the having clause, not the where clause. I'd recomment reading the appropiate documentation for the sql flavour your using
You can just use it as you did in the SELECT:
WHERE
dbo.myFunc(@param1, @param2, col1, col2) > 10
