Dean g (big_deanus@hotmail.com) writes:
> I'm trying to use the result from the select within the same query.
> See code
>
> SELECT accountnum, char(ASCII(SUBSTRING(accountnum, 1, 1))) as
> firstChar from questions where firstchar='m'
>
> Is it possible to use the result from char(ASCII(SUBSTRING(accountnum,
> 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(accountnum, 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(accountnum, 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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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

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

Is it possible to use the result from char(ASCII(SUBSTRING(accountnum,
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 ***

Thanks alot, it works perfectly

Dean


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