Using a column name in the select

[Zippeh]

Here is the SQL I need to use and I'll explain the problem underneath it!

SELECT 
	a.attachment_id,
	'"' + a.description + '"' as teitl_atodiad, 
	'"' + d.title + '"' as teitl_dogfen, 	
	'"' + a.filetype + '"' as math_atodiad, 	
	'[URL unfurl="true"]http://www.gwynedd.gov.uk/'[/URL] + a.filename as cyswllt_atodiad, 
	(SELECT TOP 1 c.cat_id 
		FROM category_list cl
			INNER JOIN categories c ON cl.cat_id = c.cat_id
		WHERE 
			cl.doc_id = d.doc_id
			AND c.parents LIKE '%|2|%'
	) as rhif_categori,
	'[URL unfurl="true"]http://www.gwynedd.gov.uk/gwy_doc.asp?catid='[/URL] + CAST(rhif_categori as varchar) + '&docid=' + CAST(d.doc_id as varchar) as lleoliad
FROM attachments a
INNER JOIN doc_attachments da ON a.attachment_id = da.attachment_id
INNER JOIN documents d ON da.doc_id = d.doc_id
WHERE 
	d.publish = 'P' 
	AND (DATEDIFF(d, d.display_to, GETDATE()) < 0 OR d.display_to is Null)
	AND (DATEDIFF(d, d.expires, GETDATE()) < 0 OR d.expires is Null)
	AND a.language_id = 1
	AND EXISTS (
		SELECT c.cat_id 
		FROM category_list cl
			INNER JOIN categories c ON cl.cat_id = c.cat_id
		WHERE 
			cl.doc_id = d.doc_id
			AND c.parents LIKE '%|2|%'
	)
ORDER BY a.attachment_id, teitl_dogfen, teitl_atodiad, math_atodiad

I need to use the column returned as "rhif_categori" in the last column of the main select statement to form a url. Is there any way I can do this?

 

[swampBoogie]

You can put the expression in a derived table, e.g.

select '[URL unfurl="true"]https://www'[/URL] + dt.expr 
from (select <complex expression> as expr
from t) as dt

contrieved but you should get the idea.