Motoko Motoko - 1 year ago 68
SQL Question

SQL Server : column prefix does not match with a table name

Here is my query. I keep getting the same error

The column prefix 'A' does not match with a table name or alias name used in the query.

although the query seems right to me

Declare @date1 nvarchar(50) = '2015-12-31'
Declare @date2 nvarchar(50) = '2016-03-31'

N'SELECT financial_element_name, financial_structure_value, [' + @date2 + '] , [' + @date1 + ']
FROM (SELECT A.[financial_structure_value], A.financial_structure_id,
A.financial_analysis_session_id, A.period
FROM counterparty_financial_structure A
INNER JOIN financial_analysis_session FAS ON FAS.financial_analysis_session_id = A.financial_analysis_session_id
WHERE A.counterparty_id = 8736) AS Source_table
(max(A.[financial_structure_value]) for A.period in (['+@date2+'] , ['+@date1+']) ) AS PivotTable;'

EXEC (@query)

Any suggestions?

Answer Source

The pivot is on the subquery, which is called source_table, so the problem is this line:

PIVOT  (max(A.[financial_structure_value]) for A.period in (['+@date2+'] , ['+@date1+']) ) AS PivotTable;

You can just use:

PIVOT  (max([financial_structure_value]) for period in (['+@date2+'] , ['+@date1+']) ) AS PivotTable;

Note: When you have errors with dynamic SQL, if you print out the SQL after variable substitution, you'll be able to easily spot the error about 95% of the time.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download