I have a while loop that runs over several years of data. During this time one attribute in some tables I need to join to has changed its name. To get around this I thought I could use a case or if statement. But I'm unable to get the Boolean expression to understand or accept the value in my macrovariable.
Very simplified (with a very short period just for example and all the other code in the while loop is working) my code looks like this:
declare @StartYear smallint = 2010
declare @Year smallint = @StartYear
declare @EndYear smallint = 2013
declare @YearVC varchar(4) = ''
declare @CreateTable varchar(MAX) = ''
while @Year <= @EndYear
begin
set @YearVC = cast(@Year as varchar)
set @CreateTable = '
if ' + @Year + ' < 2011
select t1.*, t2.a, t2.b, t2.c as e /*Variable t2.e change it´s origin name over time.*/
into ResultTable_' + @YearVC + '
from TableOne_' + @YearVC + ' as t1 left join TableTwo_' + @YearVC + ' as t2 on t1.a=t2.a
if ' + @Year + ' >= 2011
select t1.*, t2.a, t2.b, t2.d as e /*Variable t2.e change it´s origin name over time.*/
into ResultTable_' + @YearVC + '
from TableOne_' + @YearVC + ' as t1 left join TableTwo_' + @YearVC + ' as t2 on t1.a=t2.a
'
exec (@CreateTable)
set @Year = @Year + 1
end
I've also tried with a case statement (all the variable declarations, the while-loop and the @CreateTAble statement is the same so I just write my select syntax below):
select t1.*, t2.b,
case
when ' + @Year + ' < 2011 then t2.c
else t2.d
end as e
into ResultTable_' + @YearVC + '
from TableOne_' + @YearVC + ' as t1 left join TableTwo_' + @YearVC + ' as t2 on t1.a=t2.a
Both these examples give me a Msg 245 error:
Conversion failed when converting the varchar value '
with references to my Boolean expressions?
I've made certain in several and different ways that the macrovariable value that I send to that expression is not a varchar. When I try with a varchar variable I get a Msg 207 error instead:
Invalid column name 'd'.
This means that it can't find the attribute name for the attribute which will be valid from 2011 and later.
What am I missing here? How should I define my "if" or "case" statement so that SQL-server will understand that for the years before 2011 it should look for the attribute c and for the years from 2011 and further it should look for attribute d?
I use MS SQL-server version 14.
Aucun commentaire:
Enregistrer un commentaire