sql server query problem

[Dean g]

Hi,
I'm trying to use the result from the select within the same query.
See code

SELECT accountnum, char(ASCII(SUBS TRING(accountnu m, 1, 1))) as
firstChar from questions where firstchar='m'

Is it possible to use the result from char(ASCII(SUBS TRING(accountnu m,
1, 1))) within the same query without any nested loops, like i'm trying
to use it?

Thanks,
Dean

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Re: sql server query problem

Dean g (big_deanus@hot mail.com) writes:

I'm trying to use the result from the select within the same query.
See code
>
SELECT accountnum, char(ASCII(SUBS TRING(accountnu m, 1, 1))) as
firstChar from questions where firstchar='m'
>
Is it possible to use the result from char(ASCII(SUBS TRING(accountnu m,
1, 1))) within the same query without any nested loops, like i'm trying
to use it?

Use a derived table:

SELECT accountnum, firstchar
FROM (SELECT accountnum, substring(accou ntnum, 1, 1) AS firstchar
FROM questions) AS s
WHERE firstchar = 'm'

In SQL 2005 you can also use a common-table expression (CTE):

WITH extract AS (
SELECT accountnum, substring(accou ntnum, 1, 1) AS firstchar
FROM questions
)
SELECT accountnum, firstchar
FROM extract

This may look a little clunky, but keep in mind that SQL Server will
compute it more directly.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Dean g]

Re: sql server query problem


Thanks alot, it works perfectly

Dean


*** Sent via Developersdex http://www.developersdex.com ***