CODEWITHSUNDEEP
sql-server-2005
Soham Dasgupta
Soham Dasgupta

Reputation: 5199

How can I use a subquery as alias in SQL Server 2005

This is my code. Its giving me an error.

select 
b.bill_no as 'Bill Number',
(select descript from SALE_TERMS where STERMS_CODE='99')=b.[99]
from BILLDET as b

Upvotes: 1

Views: 1960

Answers (3)

Prutswonder
Prutswonder

Reputation: 10064

Your example is almost right, except for the assignment, it should be an 'AS' statement containing the column name. Also, it's a good thing to use TOP 1 in your subquery, in case the STERMS_CODE value is not unique:

SELECT
b.bill_no AS 'Bill Number',
(SELECT TOP 1 descript from SALE_TERMS WHERE STERMS_CODE='99') AS [99]
FROM BILLDET AS b

Upvotes: 1

gbn
gbn

Reputation: 432210

SELECT
   b.bill_no as 'Bill Number',
   ST.[99]
FROM
   BILLDET as b
   CROSS JOIN
   (SELECT descript AS [99] FROM SALE_TERMS WHERE STERMS_CODE = '99') ST

or do you meanthis?

SELECT
   b.bill_no as 'Bill Number',
   ST.descript
FROM
   BILLDET as b
   JOIN
   SALE_TERMS st ON b.[99] = ST.STERMS_CODE
WHERE
   ST.STERMS_CODE = '99'

Upvotes: 1

marc_s
marc_s

Reputation: 754278

Why not just:

SELECT
   b.bill_no as 'Bill Number',
   (SELECT descript FROM SALE_TERMS WHERE STERMS_CODE = '99') AS 'Code99'
FROM
   BILLDET as b

Upvotes: 2

Related Questions