Forum Discussion

Siva_Venna's avatar
Siva_Venna
Contributor
6 years ago

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 ?

17 Replies

      • SpiroTaleski's avatar
        SpiroTaleski
        Valued Contributor

        @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.

  • del's avatar
    del
    Contributor III

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

    • Siva_Venna's avatar
      Siva_Venna
      Contributor

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

  • christwr's avatar
    christwr
    Contributor III

    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?

  • dmiller's avatar
    dmiller
    Former Employee

    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.

  • jcampion's avatar
    jcampion
    New Contributor III

    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.

    • Siva_Venna's avatar
      Siva_Venna
      Contributor

      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 ?

      • jcampion's avatar
        jcampion
        New Contributor III

        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