Table name in SQL select snap

Hi, I have few tables on SQL server like
abc_2020
abc_2019
abc_2018
I want to query the the table that has suffix as current year. I have specified the value
in the settings > table name-- ’abc_’+ Date.now().getFullYear()
it’s throwing error.
Failure: Failed to retrieve metadata for table: “‘abc_’+Date”.“now()”.“getFullYear()”, Reason: Database ‘now()’ does not exist. Make sure that the name is entered correctly., Resolution: Database ‘now()’ does not exist. Make sure that the name is entered correctly.

When I hard-code the table name as abc_2020 it is working.
Can someone help me with this ?

Hi @Siva_Venna

Can you please provide screenshot of the Snap where you are passing the table name?

Regards,
Spiro

Make sure you’re toggling ‘on’ the expression icon image

@del Yeah I am aware of that, I have toggled it before posting the query here

@Spiro_Taleski Here you go
Capture

@Siva_Venna

Can you please check if table that you want to query exist in the database and remove the brackets before Date.now() and after getFullYear().

Also, you can try first to select the database schema name and then pick the appropriate table.

@Spiro_Taleski I have checked manually the table exists and when I hard code the table name I am getting the records. As you suggested removed the brackets yet same error.the output preview is as expected.
Capture1

@Siva_Venna

Did you try to pass a table name value from the upstream mapper snap?

Seems like “failed to retrieve metadata” would be more about preview and the snap trying to pull and suggest fields and such for where clause, etc. Do you get this error when you actually run/execute the pipeline, or only when previewing/configuring?

@christwr I am getting this error when previewing and executing.

I checked with Development and it looks like there is an error here already being investigated.
We’ll update this thread when it is addressed.

For now, you could use an upstream mapper snap to generate the table name, and then just use it, e.g. $tableName, from the SQL Select snap.

Thanks @Spiro_Taleski @jcampion
For now I have used mapper snap and it worked.
In the mapper I have created(tablename+year) - abc_Date.now().getFullYear() - $tablename
Instead, if I create only date part as variable Date.now().getFullYear() - $year How to pass this in table name filed in Select snap?
Table name = ‘abc_’+$year ?

Hi @Siva_Venna,
I think that would work. It seems like there’s a bug in the SQL snap’s expression evaluation where it’s choking on Date.now(), but if you already have the $year field I would think you can use ‘abc_’ + $year to get the tablename. Give it a try!

  • Judy

I gave a try it did not work. Hope the fix will resolve the issue.