0

I have the following query, which I rewrote as a 'simple' example below.

The expression CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c is just a weird example of a complex function.

I am questioning myself: Why do I have to put this complex expression 3 times in my query?

DROP TABLE #TEMP
CREATE TABLE #TEMP (a int, b int, c int)

INSERT INTO #TEMP VALUES( 1,1,1)
INSERT INTO #TEMP VALUES( 1,1,2)
INSERT INTO #TEMP VALUES( 1,1,3)
INSERT INTO #TEMP VALUES( 1,2,1)
INSERT INTO #TEMP VALUES( 1,2,2)
INSERT INTO #TEMP VALUES( 1,2,3)
INSERT INTO #TEMP VALUES( 2,1,1)
INSERT INTO #TEMP VALUES( 2,1,2)
INSERT INTO #TEMP VALUES( 2,1,3)
INSERT INTO #TEMP VALUES( 2,2,1)
INSERT INTO #TEMP VALUES( 2,2,2)
INSERT INTO #TEMP VALUES( 2,2,3)



SELECT 
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
GROUP BY a, b, CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c
HAVING CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c >= 0
ORDER BY 3 DESC

The question is mainly for using in SQL Server, but answers for other database management systems are welcome too.

  • If you ask why you can't use a column alias in the GROUP BY and HAVING clauses, then the answer is that the column alias is done after those operations. Meaning that column alias doesn't exist at the "time" the GROUP BY and HAVING work with the data. And, as suggested, you can handle this using a tabular subquery having your expression. See for instance this on logical query processing: tech-recipes.com/rx/60250/logical-query-processing-sql-server – Tibor Karaszi Oct 19 '18 at 6:43
1

You don't have to. You can just rewrite it to CTE/inline view , and refer by alias :

SELECT t.* FROM 
(
SELECT 
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
)t
GROUP BY a,b,d
HAVING d > 0 
ORDER BY 3 ;

or

WITH cte1 AS 
(
SELECT 
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
)
select t.* 
from cte1 t
group by t.a,t.b,t.d 
having t.d > 0 
order by 3; 

Also, there is no need for GROUP BY( and HAVING can be replaced by WHERE) in this particular case - SELECT DISTINCT will do the job.

  • " in this particular case".... my query was just an example, so this remark does not count ;) – Luuk Oct 19 '18 at 19:08
  • But, yes, on could do it the way you proposed. But maybe it's a bit confusing to put this extra syntax for an easy job... (aliasing an expression once, and referring it multiple times seems easier!) – Luuk Oct 19 '18 at 19:12
  • @Luuk: Some RDBMS allow you do that . For instance, in mysql you can use alias in having and group by, in postgres - only in group by . Oracle works the same as SQLServer from that perspective. Inline view approach(#1) will work everywhere. – a1ex07 Oct 19 '18 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.