filtering with a alias

Question

filtering with a alias

Cristian Maidana-360609

SSC Rookie

Points: 27
More actions
September 2, 2010 at 8:32 am

#225506

Hi, i was trying filtering a query with an alias, but it doesn't work
select a,b, (select count(*) as Tot from table2) as Tot
From table1
where (Tot > 0).
but the alias coulumn work if i using it in "order by".
can anybody help me to understand what's happens?
Tks in advance.
Cristian.-

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Cristian Maidana-360609 (9/2/2010)
Hi, i was trying filtering a query with an alias, but it doesn't work
select a,b, (select count(*) as Tot from table2) as Tot
From table1
where (Tot > 0).
but the alias coulumn work if i using it in "order by".
can anybody help me to understand what's happens?

Brute force solution?

select * from

(

select a,b, (select count(*) as Tot from table2) as Tot

From table1

)

where (Tot > 0)

Having said that, are you aware the value of "Tot" will be always the same?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Cristian Maidana-360609 SSC Rookie Points: 27 More actions · Answer 2

Hi, thanks for your answer.

bellow the original Query, when i implemented your suggestion, i have the "Msg 102, Level 15, State 1, Line 12

Incorrect syntax near ')'." error message.

any idea what is the problem?

Tks in advance.

NOTE: TotProp retrieve differents values.

Query

SELECT * FROM

(

SELECT tEmpresa.idEmpresa,

(select COUNT(*) as [TotProp] FROM t_INMO_Poi_Inmobiliario

WHERE (idEmpresa = tEmpresa.idEmpresa) AND (activo = 1) group by

idempresa) as [TotProp], tEmpresa.RazonSocial, tEmpresa.NombreCalle, tEmpresa.NroCalle,

tEmpresa.DetalleDomicilio, tEmpresa.TelefonoCodCiudad,tEmpresa.Telefono, tEmpresa.Telefono2CodCiudad,

tEmpresa.Telefono2, tEmpresa.web, tEmpresa.email

FROM tEmpresa INNER JOIN

tUsuario ON tEmpresa.idUsuario = tUsuario.idUsuario

WHERE (tUsuario.habilitado = 1) AND (tEmpresa.idActividad = 16)

)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Cristian Maidana-360609 (9/2/2010)
Hi, i was trying filtering a query with an alias, but it doesn't work
select a,b, (select count(*) as Tot from table2) as Tot
From table1
where (Tot > 0).
but the alias coulumn work if i using it in "order by".
can anybody help me to understand what's happens?
Tks in advance.
Cristian.-

[Tot] is an output column. ORDER BY works on output columns - as you would expect.

The WHERE clause works on input columns to select the rows you want - again as you would expect.

Get the green balls out of the box then paint them red

vs

Paint the green balls red then get them out of the box.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Cristian Maidana-360609 (9/2/2010)
Hi, thanks for your answer.
bellow the original Query, when i implemented your suggestion, i have the "Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'." error message.
any idea what is the problem?
Tks in advance.
NOTE: TotProp retrieve differents values.
Query
SELECT * FROM
(
SELECT tEmpresa.idEmpresa,
(select COUNT(*) as [TotProp] FROM t_INMO_Poi_Inmobiliario
WHERE (idEmpresa = tEmpresa.idEmpresa) AND (activo = 1) group by
idempresa) as [TotProp], tEmpresa.RazonSocial, tEmpresa.NombreCalle, tEmpresa.NroCalle,
tEmpresa.DetalleDomicilio, tEmpresa.TelefonoCodCiudad,tEmpresa.Telefono, tEmpresa.Telefono2CodCiudad,
tEmpresa.Telefono2, tEmpresa.web, tEmpresa.email
FROM tEmpresa INNER JOIN
tUsuario ON tEmpresa.idUsuario = tUsuario.idUsuario
WHERE (tUsuario.habilitado = 1) AND (tEmpresa.idActividad = 16)
) d

You're missing the alias for the derived table (d)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cristian Maidana-360609 SSC Rookie Points: 27 More actions · Answer 5

EXCELENT! NOW IT'S WORKING!!!

Thanks to all!

Have a nice day!

Cristian..