CODEWITHSUNDEEP
sqlsql-server
Motoko
Motoko

Reputation: 87

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?

Upvotes: 0

Views: 1703

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

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.

Upvotes: 1

Related Questions