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
views:
44answers:
3
+2
A:
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
marc_s
2010-03-19 11:14:43
A:
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'
gbn
2010-03-19 11:16:21
A:
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
Prutswonder
2010-03-19 11:21:18
What I want to do is use the subquery as column name not row value. Your answer is doing the same.
Soham Dasgupta
2010-03-19 11:48:17
I see. You want to create a dynamically named column based on a subquery. That's not going to work this way. Can you provide some sample input and output data?
Prutswonder
2010-03-19 13:55:25
there is no way this ever works. A SQL Statement assumes a "proper", i.e. stable, environment after compilation. You could do it with dynamic SQL,. but it takes 2 queries - one o get the field name, then some string magic, thne one to get the data.
TomTom
2010-03-19 14:51:10
So I have to use a cursor right?
Soham Dasgupta
2010-03-21 16:17:14