use alias column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

use alias column

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2014-03-18 : 14:35:59

Hi,

not sure why this query didn't work because I of the alias X, please help -thanks
WITH documents AS (
select distinct
a.idNumber_int,
a.localeFKey_int,
select * from
(select ISNULL(STUFF((SELECT ', '+case when c.companyFKey_int = 1000 then 'Center' else ltrim(rtrim(
d.companyName_var)) end
from Company c left join Mgmt.dbo.companyNames d on c.companyFKey_int = d.companyFKey_int
and d.companyNameTypeFKey_smint = 2
where c.documentFKey_int = a.idNumber_int
for xml path('')),1,2, ''),'ALL') as companyName
) as x,
ROW_NUMBER() OVER (ORDER BY x desc) AS RowNumber

from doc a
left join Company b on a.idNumber_int = b.documentFKey_int
where 1=1
and a.docCatFkey_int = 2
)
SELECT *
FROM documents

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-18 : 14:46:22

What is going on here:
a.localeFKey_int,
select * from

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

kt
Yak Posting Veteran

88 Posts

Posted - 2014-03-18 : 14:50:51

select distinct
a.idNumber_int,
a.localeFKey_int,
......
just like select fields from table.
I want to put this code into alias X so I can use this in the where clause or I can sort this column from
ROW_NUMBER() OVER (ORDER BY x desc) AS RowNumber. the problem is the syntax below with alias but can't figured out, error:Incorrect syntax near the keyword 'from'.

select * from
(
select ISNULL(STUFF((SELECT ', '+case when c.companyFKey_int = 1000 then 'Center' else ltrim(rtrim
(d.companyName_var)) end
from Company c
left join Mgmt.dbo.companyNames d on c.companyFKey_int = d.companyFKey_int and d.companyNameTypeFKey_smint = 2
where c.documentFKey_int = a.idNumber_int
for
xml path('')), 1, 2, ''),'ALL') as companyName_var
) as x

from Documents a
left join _Company b on a.idNumber_int = b.documentFKey_int
where 1=1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-18 : 15:02:56

How many rows are returned by it?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources