Converting MS Access Query to SQL; Syntax problem?

Transact-SQL
1

Can someone tell me what what I am doing wrong on this query - which is failing under SQL, but works on MS Access? It is failing on the [Budget Value] - [Allocated Spend] AS Balance; so I think I have a syntax issue but can't see it

Thanks

Phil

SELECT	ID, 
		[Budget Line], 
		[Description], 
		[Budget Value], 
		[Budget Type], 
		[Status], 
		[Contingency], 
		(Select 
			Sum(Value) From [Purchase Orders] Where BudgetID = BL.ID) 
			AS [Value of POs], 
		(Select 
			Count(*) From [Purchase Orders] Where BudgetID = BL.ID) 
			AS POs, 
		(SELECT 
			Sum([Spend Value]) 
				FROM 
				([Budget Lines] 
					INNER JOIN [Purchase Orders] ON [Budget Lines].ID = [Purchase Orders].BudgetID) 
					INNER JOIN [Project Spend] ON [Purchase Orders].ID = [Project Spend].POID  
				Where [BudgetID] = BL.ID) 
			AS [Allocated Spend],

		[Budget Value] - [Allocated Spend] AS Balance, 
		BL.Notes
FROM 
	[Budget Lines] AS BL
WHERE 
	[Project Reference]=@PROJECT
2

it is not really syntax error. [Allocated Spend] is a column alias. You make can't reference to column alias just like that.

You can however use a derived table or CTE to calculate the [Allocated Spend] and then reference it.

example

SELECT ID,
	[Budget Line],
	[Description],
	[Budget Value],
	[Budget Type],
	[Status],
	[Contingency],
	[Value of POs],
	POs,
	[Allocated Spend],
	[Budget Value] - [Allocated Spend] AS Balance,
	Notes
FROM (
	SELECT ID,
		[Budget Line],
		[Description],
		[Budget Value],
		[Budget Type],
		[Status],
		[Contingency],
		(
			SELECT Sum(Value)
			FROM [Purchase Orders]
			WHERE BudgetID = BL.ID
			) AS [Value of POs],
		(
			SELECT Count(*)
			FROM [Purchase Orders]
			WHERE BudgetID = BL.ID
			) AS POs,
		(
			SELECT Sum([Spend Value])
			FROM (
				[Budget Lines] INNER JOIN [Purchase Orders]
					ON [Budget Lines].ID = [Purchase Orders].BudgetID
				)
			INNER JOIN [Project Spend]
				ON [Purchase Orders].ID = [Project Spend].POID
			WHERE [BudgetID] = BL.ID
			) AS [Allocated Spend],
		BL.Notes
	FROM [Budget Lines] AS BL
	WHERE [Project Reference] = @PROJECT
	) d
1 Like
3

Many thanks for the quick answer. Makes sense and I won't thank Access for doing things different :slight_smile: