4 Replies Latest reply on Sep 18, 2017 11:05 AM by Yuriy Fal

    Custom SQL language

    Mary Courtney

      Wondering, how do I find out which SQL language I should be using depending on my data connection?

       

      I'm trying to create a substring of a field to connect two data sources (which I can't do in the ETL) and trying to use the very common LEFT function when connected to BigQuery is not allowing me to:

       

      SQL:

      Select Left(`Tbl_Customers`.`Name`, 1) as Initial

      From `Databoe_Area.Databoe-bucket`.`Tbl_Customers` `Tbl_Customers`

       

        • 1. Re: Custom SQL language
          Jeff Strauss

          Usually the custom SQL within Tableau will wrap the select and then will pass it directly to your database.  Is it possible that you are using the wrong database driver?  Can you see the SQL within the logs or within your database?  Can you try copying / pasting the SQL into your database GUI, does it work here?  Does it work without quotes?

           

          What I mean by wrapping is that Tableau will nest the SQL such as:

           

          select * from

          (

          Select Left(`Tbl_Customers`.`Name`, 1) as Initial

          From `Databoe_Area.Databoe-bucket`.`Tbl_Customers` `Tbl_Customers`) TableauSQL

          • 2. Re: Custom SQL language
            Mary Courtney

            I'm connecting through Tableau Desktop to Google Big Query. I've previously been using the drag and drop functionality, but now I want to add some custom sql.

             

            When I click Data>Convert to Custom SQL I don't get the wrapped "select * from (...)

             

            How do I know which language this created in so I can read up on documentation to modify it?

            • 3. Re: Custom SQL language
              Jeff Strauss

              you won't see the wrapper within the Tableau dialogue window, but if you look in the desktop log, you should be able to see the wrap there.

               

              You can find the desktop log usually at the default location of:  C:\Users\username\Documents\My Tableau Repository\Logs

              • 4. Re: Custom SQL language
                Yuriy Fal

                Hi Mary,

                 

                LEFT() function is a Legacy one.

                Please use the SUBSTR() instead, like this:

                Select SUBSTR(`Tbl_Customers`.`Name`, 1, 1) as Initial

                From `Databoe_Area.Databoe-bucket`.`Tbl_Customers` `Tbl_Customers`

                Hope it could help.

                 

                Yours,

                Yuri

                 

                 

                PS Here is a reference page from BigQuery help

                regarding the differences between Standard and Legacy SQL:

                 

                Migrating to Standard SQL  |  BigQuery  |  Google Cloud Platform 

                 

                1 of 1 people found this helpful