Hi,

It's possible to use a alias name directly in a sql statement?
Example:

        SELECT Col1 * Col2 AS alias, alias * Col3 FROM table

best regards
Jan

AFAIK, no in Sql2k
Show quoteHide quote

Why not?
CREATE TABLE #Test (col1 INT, col2 INT,col3 INT)
INSERT INTO #Test VALUES (20,30,50)

SELECT alias * col3 FROM
(
SELECT col1 * col2 AS alias,col3 FROM #Test
) AS Der




Show quoteHide quote

you're right, I don't know what i was thinking
Show quoteHide quote

I wouldn't call that an alias, at least not in this context.

It is a column name in an inline view, which is different from accessing an
alias within the same select in which it is defined.

While Uri's example is compeltely accurate, it is very different from what
the original poster seemd to be asking.

I think you can reference an alias in an order by (is this SQL Server
specific?), but not in any other part of a sql statement.

Meaning you cannot define the alias and reference it in the same select
clause, the where clause, or the group by clause.

You can do it as Uri has shown if you reference it outside of an inline
query.

I hope this answers the question without being too confusing.

wrong syntax:

SELECT count(*), Col1 * Col2 AS alias
, alias * Col3 as alias2 -- not allowed
FROM table
where alias = 12 -- not allowed
group by alias, alias2 -- not allowed

Correct syntax:

SELECT count(*), Col1 * Col2 AS alias
, Col1 * Col2 * Col3 as alias2
FROM table
where Col1 * Col2  = 12
group by Col1 * Col2 , Col1 * Col2 * Col3

also correct syntax (using Uri's example):

select alias, alias2 From
(
   SELECT alias, alias * col3 as alias2 FROM
   (SELECT col1 * col2 AS alias,col3 FROM #Test) AS Der
) AS derived2
where alias = 12
group by alias, alias2

Show quoteHide quote

How can I check if it is currently executing?
Left Joins (*=)
killed/rollback stuck on object_name(99)
Select if... endif
Multiple queries in stored procedure questions