3 Replies Latest reply on Apr 26, 2017 2:24 PM by Chris McClellan

    Defining Variable Help

    Jason Hanser

      I'm trying to execute a simple query in Tableau (below), but I keep getting an error.

       

      DEFINE Next_Year = '2018'

      SELECT Table_1.*

      FROM Table_1

       

      This query works when I run it Oracle SQL Developer and it work in Tableau when I remove the DEFINE statement, but when I include the DEFINE statement in Tableau I get the following error:

       

      "Oracle database error 907: ORA-00907: missing right parenthesis"

       

      Does anyone know what the issue it?

        • 1. Re: Defining Variable Help
          Chris McClellan

          Why do you want to define a variable in SQL before running the SELECT .... and not actually using the variable ?

           

          It also looks like you're using Custom SQL - no real need for that either

           

          Basically, you can't declare a variable like that in Tableau, and what are you going to do with it if you could ?

          • 2. Re: Defining Variable Help
            Jason Hanser

            Sorry, I should have been more clear. This is a simplified version of the query and the variable is used. And, I use it to transform another variable which I filter on, which is why I can't just declare it in Tableau.

            • 3. Re: Defining Variable Help
              Chris McClellan

              There's no way around it though.  If you're using Custom SQL then declare the parameter in Tableau and pass the value to Oracle.  Within the SELECT statement you have to modify the value.

               

              Tableau always "wraps" the SQL in a subquery, which is why it doesn't work, because this doesn't work in Oracle either (at a guess)

               

              SELECT * FROM

              (

                   DEFINE Next_Year = '2018'

                   SELECT Table_1.*

                   FROM Table_1

              ) A