Motoko Motoko - 3 months ago 22
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'

DECLARE @query AS NVARCHAR(max) = 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

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

exec (@query)


Any suggestions?

Answer

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.

Comments