Wrong entrance

Sometimes I think with the wrong body part or put into Swedish - ' Ibland tänker jag med arschlet'.

I got this question, how do I transfer a MYSQL macro variable into an ITL @tag? Like this:

set @THISYR := DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY),'%Y'); I want to use the value in @THISYEAR to create a column in a table like this: CREATE TABLE IF NOT EXISTS ANNA_CAKE_TEST( VALUE_@THISYEAR char(10))

After some (2 hours) research I realized it is not easy to grab the MySQL macro variable @THISYEAR and transfer it into an ITL @TAG. I started to look for alternative solutions. When you start to look on a tough problem from another angle, you often find a simple solution that seems so obvious when it's found, here is one simple ETL solution:

<job> <forevery> select DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY),'%Y') as THISYEAR from DUAL ; </forevery> <sql> CREATE TABLE IF NOT EXISTS ANNA_CAKE_TEST( VALUE_@THISYEAR char(10)) </sql> </job>

It is as my former colleague Ulf Davidsson used to say ‘It’s about the entrance, if you enter a problem from the wrong direction, you will create the wrong solution’.

While I was searching for the wrong solution, the problem was solved in a stored procedure. All solutions that work are good solutions, well almost anyway.