Geek Answers Handbook

SELECT AS Problem

I tried to execute the following query:

SELECT t1.[user1], t1.[user2], (CAST(t1.[total_event_duration] AS DECIMAL)) / (CAST (t2.[total_events_duration] AS DECIMAL)) AS buddy_strength FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 INNER JOIN [CDRs].[dbo].[user_monthly_stats] AS t2 ON t1.[user1] = t2.[user1] WHERE buddy_strength > 0.02

But it returns the error "Invalid column name" buddy_strength ""

Does anyone know how to fix the request above?

+4
source share
4 answers
 SELECT * FROM ( SELECT t1.[user1], t1.[user2],(CAST(t1.[total_event_duration] AS DECIMAL))/(CAST (t2.[total_events_duration] AS DECIMAL)) AS buddy_strength FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 INNER JOIN [CDRs].[dbo].[user_monthly_stats] AS t2 ON t1.[user1] = t2.[user1] ) foo WHERE foo.buddy_strength > 0.02
+7
source

You cannot use aliases in WHERE . You need to repeat the whole expression ( CAST(t1.[total_event_duration] AS DECIMAL))/(CAST (t2.[total_events_duration] AS DECIMAL)>0.02 ).

+5
source

You cannot use aliases in where , group by or having . You can get around this by wrapping it in a subquery:

 SELECT * FROM ( SELECT t1.[user1], t1.[user2], (CAST(t1.[total_event_duration] AS DECIMAL)) / (CAST (t2.[total_events_duration] AS DECIMAL)) AS buddy_strength FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 INNER JOIN [CDRs].[dbo].[user_monthly_stats] AS t2 ON t1.[user1] = t2.[user1] ) a WHERE a.buddy_strength > 0.02

Otherwise, you will have to re-enter all this, which is not good.

+3
source

You cannot use columns with an alias in the where clause. I think you will have to reproduce the value of this derived field in the where clause, for example:

 SELECT t1.[user1], t1.[user2],(CAST(t1.[total_event_duration] AS DECIMAL))/(CAST (t2.[total_events_duration] AS DECIMAL)) AS buddy_strength FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 INNER JOIN [CDRs].[dbo].[user_monthly_stats] AS t2 ON t1.[user1] = t2.[user1] WHERE (CAST(t1.[total_event_duration] AS DECIMAL))/(CAST (t2.[total_events_duration] AS DECIMAL)) > 0.02
+2
source

More articles:


All Articles