0
select  (ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT))))) as parameter ,LAYOUT,FORM_NAMESPACE_NAME from [FORM_LAYOUT]

group by  WIDTH,HEIGHT,LAYOUT,FORM_NAMESPACE_NAME

order by ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT)))) desc

this works like that.

but i want to put a parameter. i will send this parameter in my program (c#) .

let's say i put my parameter 500, so it must work like

(ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT))))) as parameter<500

in order to achieve that

select  (ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT))))) as parameter ,LAYOUT,FORM_NAMESPACE_NAME from [FORM_LAYOUT]

where parameter <500
group by  WIDTH,HEIGHT,LAYOUT,FORM_NAMESPACE_NAME

order by ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT)))) desc

doesn't work. It says "invalid column name parameter"

how can i correct it.

Thanks.

mdy25307
  • 3
  • 2
  • 3
    You cannot use alias in where clause – Nisfan Mar 19 '19 at 10:49
  • Aliases cannot be used in where clause, you need to do a workaround. Check this SO post: https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause – prinkpan Mar 19 '19 at 10:50

2 Answers2

0

Query should be

select  (ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT))))) as parameter ,LAYOUT,FORM_NAMESPACE_NAME from [FORM_LAYOUT] 
group by  WIDTH,HEIGHT,LAYOUT,FORM_NAMESPACE_NAME
having ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT)))) < 500    
order by ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT)))) desc
Nisfan
  • 750
  • 6
  • 10
  • it says "Msg 147, Level 15, State 1, Line 17 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference." – mdy25307 Mar 19 '19 at 10:56
  • I have updated anwer, You should use having clause if you want to filter with grouping columns – Nisfan Mar 19 '19 at 11:25
0

You can accomplish this in two ways.

1) change your where statement to the following

WHERE (ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT))))) < 500
GROUP BY LAYOUT, FORM_NAMESPACE_NAM

2) Use a sub query

SELECT [parameter]
FROM (SELECT (ABS(MAX((convert(int,WIDTH))- (convert(int,HEIGHT))))) as [parameter])
WHERE parameter < 500
Monofuse
  • 735
  • 6
  • 14
  • it says "Msg 147, Level 15, State 1, Line 17 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference." – mdy25307 Mar 19 '19 at 10:56