6 Replies Latest reply on Jul 28, 2016 4:00 AM by Mark Fraser

    Custom SQL

    Laura Vecchio

      Good Morning,

      As a starter, I'm completely new to Tableau and SQL so apologies for what may be a crude explanation!

       

      I'm trying to merge multiple databases with each database containing multiple brands.

      My issue is, within each database the company ID starts at 0 so I can't merge them without telling SQL to replace the 0's in all but one of the databases to return an alternative 'company ID'.

       

      An example hierarchy would be;

      Database A - Digital (0), Search (1), PPC (2)

      Database B - Advertising (0)

       

      The two tables containing company info are;

      [system_control].[company_id]

      [placements].[owning_company_id]

       

      But these are also how the tables are joined;

      INNER JOIN [dbo].[placements] [placements] ON ([system_control].[company_id] = [placements].[owning_company_id])

       

      My basic SQL knowledge told me to use a CASE statement. So I included the following in database B's SQL script'

       

      CASE[system_control].[company_id]

      WHEN 0 THEN 100

      END,

       

      CASE[placements].[owning_company_id]

      WHEN 0 THEN 100

      END,

       

      Within the results Advertising now has company ID of 100 and the company name reads 'Advertising' however the records it's returning are those of Digital.

       

      Therefore when I run a query on billings (a calc field) I get the following;

      Digital - £49,500

      Search - £25,000
      PPC - £10,000

      Advertising - £49,500

       

      So it's recognising the company name and ID I've asked it to from database B but it's getting it confused with database A.

       

      I have a feeling I perhaps need to make amendments to the join?
      I've tried Google and forums but nothing seems to work.

       

      Any help would be hugely appreciated as at present I can't run any complete reports!

      Thank you
      Laura

        • 1. Re: Custom SQL
          Mark Fraser

          Hi Laura

           

          That's quite a question

          Let's start at the beginning, the company names...

          My issue is, within each database the company ID starts at 0 so I can't merge them without telling SQL to replace the 0's in all but one of the databases to return an alternative 'company ID'.

          If you are joining on companyid, we need to ensure it is consistent across the tables, otherwise the join will fail/ give unexpected results.

          Am i to understand that this currently isn't the case? i.e the companyid doesn't always match?

          And if so, is it just a 0 at the front? so 0123 vs 123? or something else?

           

          You may also consider a left outer join, but we can come to that

          • 2. Re: Custom SQL
            Mark Fraser

            Would you have a small sample you could provide? of each table, in Excel?

            • 3. Re: Custom SQL
              Laura Vecchio

              Oh dear! I was hoping you wouldn't say that. . . . .!

               

              No, the Company ID is just that, "0".

              The next subsidiary within that same database would be "1", the next "2" etc.

              It's then when I want to merge another database that two different subsidiaries have the same company ID - "0"

               

              Fortunately it's only database A that has multiple subsidiaries (the rest are singular subsidiaries on singular databases, albeit all with company ID "0") so we wouldn't have to convert multiple company IDs within the other databases if that makes sense?

               

              We literally just have to covert;

              Database B - company 0 to company 100

              Database C - company 0 to company 200 etc.

               

              I've attached a example of the Custom SQL (with my woeful case statement included!) along with an example of the results both before & after the addition of the case statement.
              As you can see it has changed the Company ID to "100" but is returning the records for Company ID "0"

               

              I'm really unsure of the next step!

               

              Any help would be hugely appreciated
              Kindest regards,
              Laura

              • 4. Re: Custom SQL
                Mark Fraser

                Hi Laura

                 

                OK thanks, that helps.

                So initially your taking an extract of data from database A, and the same extract from database B (I assume they are the same because of the UNION)

                You then union them, I presume to make a single data set/ table for reporting? That's all fine.

                 

                The samples you gave - is 1 from A, and the other B? < that's what I would like

                Or a before and after from the case statement?

                 

                Maybe this example helps (if it confuses, ignore it!)

                https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

                 

                Cheers

                Mark

                • 5. Re: Custom SQL
                  Mark Fraser

                  FYI - Because you're new, you'll get moderated, its nothing personal.

                  I'm a forum admin, so just reply, and I'll make sure your reply gets through.