Avoiding recomputing values in WHERE statement already computed in column list

Question

I would really love to not have to recompute values in my WHERE statement that have already been computed in the column list.

For example, I have to do this:

SELECT albumId, fnAlbumGetNumberOfPhotos(album.albumId) AS albumPhotoCount
FROM album
WHERE albumIsActive = 1 
AND fnAlbumGetNumberOfPhotos(album.albumId) > 0

But would like to know why I can't do it like this, or if there are any other workarounds that don't require calling the function again:

SELECT albumId, fnAlbumGetNumberOfPhotos(album.albumId) AS albumPhotoCount
FROM album
WHERE albumIsActive = 1 
AND albumPhotoCount > 0

This is a simplified version of a much more complex query, but it illustrates the problem. Surely there must be a way to handle this without multiple function calls??

Help is greatly appreciated. Thanks

Answer 1

Use HAVING.

SELECT albumId, fnAlbumGetNumberOfPhotos(album.albumId) AS albumPhotoCount
FROM album
WHERE albumIsActive = 1 
HAVING albumPhotoCount > 0

HAVING is calculated after the SELECT, when albumPhotoCount exists.