SQL: How to (WHERE) column (SUM) by caption or index?-CodePudding

How do I make a condition (WHERE) then a column caption or index which is contained some functions like this:

SELECT COLUMN_1 AS [COLUMN_1_Val], 
COLUMN_2 AS [COLUMN_2_Val], 
SUM(A B-C-D)*E*F/G -SUM(H-I)*K/L AS [My_Complicated_Function], 
COLUMN_3 AS [COLUMN_3_Val]

WHERE [My_Complicated_Function]>0

How do I call my function column in WHERE clause or even by its index like (WHERE columnindex(2)>0)

CodePudding user response：

Just put your first part in subquery

SELECT * FROM 
(
   SELECT COLUMN_1 AS [COLUMN_1_Val], 
   COLUMN_2 AS [COLUMN_2_Val], 
   SUM(A B-C-D)*E*F/G -SUM(H-I)*K/L AS [My_Complicated_Function], 
   COLUMN_3 AS [COLUMN_3_Val]
   FROM ... 
)
WHERE [My_Complicated_Function]>0

CodePudding user response：

Lets say ,you have a function func_Sum as follows,

create function func_Sum  
(  
 @Num int   
)  
returns int 
as  
begin 
return(select @Num @Num)  
end

now you can use this function in where clause like this,

SELECT 1 AS [COLUMN_1_Val], 
2 AS [COLUMN_2_Val], 
TEST.dbo.func_Sum(5) AS [my_func], 
5 AS [COLUMN_3_Val]
WHERE TEST.dbo.func_Sum(5) >0