Subquery in Select list item aliased and used in expression 
Author Message
 Subquery in Select list item aliased and used in expression

I am upsizing an Access 97 database to SQL Server 7 Standard Edition.

There are a few QueryDefs that are giving me headaches.  Access (JetSQL)
allows an aliased singleton subquery in a select list item to be used in
expressions in other select list items, but it appears that SQL Server
(T-SQL) will not.  Example:

SELECT
    (SELECT Top 1 foo FROM bar WHERE...) As Expr1,
    Col1,
    Col1 + Expr1 As Expr2
FROM ....

From my analysis so far, it does not look like I can just integrate the
subquery in to the main query through joins.  I have been looking at
using temp tables, cursors, etc, but I keep thinking that there has to
be a more elegant way.

Thanks in advance,

-Peter

--
"Funkify your life" - The Funky Meters
-----------------------------------
Peter Daniels - Husband, Database Geek, Tribes junkie



Sun, 14 Jul 2002 03:00:00 GMT
 Subquery in Select list item aliased and used in expression

Peter,

The following works fine for me:

use pubs
go

select (select top 1 au_id from authors) as Top1, au_id, au_lname, au_fname
from authors

I am running SQL Server 7.0 w/SP1. Note that by default the pubs database is
in 70 compatibility mode. If the preceding sentence doesn't mean anything to
you, then you might check out the section "sp_dbcmptlevel (T-SQL)" in the
SQL Server 7.0 Books Online.


Quote:
> I am upsizing an Access 97 database to SQL Server 7 Standard Edition.

> There are a few QueryDefs that are giving me headaches.  Access (JetSQL)
> allows an aliased singleton subquery in a select list item to be used in
> expressions in other select list items, but it appears that SQL Server
> (T-SQL) will not.  Example:

> SELECT
>     (SELECT Top 1 foo FROM bar WHERE...) As Expr1,
>     Col1,
>     Col1 + Expr1 As Expr2
> FROM ....

> From my analysis so far, it does not look like I can just integrate the
> subquery in to the main query through joins.  I have been looking at
> using temp tables, cursors, etc, but I keep thinking that there has to
> be a more elegant way.

> Thanks in advance,

> -Peter

> --
> "Funkify your life" - The Funky Meters
> -----------------------------------
> Peter Daniels - Husband, Database Geek, Tribes junkie




Sun, 14 Jul 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. more than one expression in a select subquery

2. Select nth item in a select list...

3. ORDER BY items must appear in the select list if SELECT DISTINCT is specified

4. Selecting Items from a multi select list box VC++ 6.0 on W2K

5. Selecting items in a DB-list and passing fields from selected rec to a control

6. selecting items in a Data bound list and passing a field content for the selected record

7. Selecting more than 1 item from nested subquery

8. Subquery used as an expression?

9. Problem with subquery cant access top level aliased table

10. select statement using :variable as selected item

11. subquery in SELECT list?

12. SELECT subquery much slower than IN ( list...)???


 
Powered by phpBB® Forum Software