8 Replies Latest reply on Sep 20, 2017 12:38 PM by minc650

    How to use custom sql to join or union all these tables

    minc650

      Hi everybody,

       

      As discussed before about my current project, I have 9 tables, 6 of them are aggregated tables, the other 3 tables  are detailed table. Each table needs to join with the reference table, the front_store,  to get the geo cd ( location code).

      At first, I use  data blending to join these tables. For performance, I have to extract each table. For aggregated tables, the number of row are from 500 to 2000 rows, so it is pretty fast to do the extracting. But  each detailed table has about tens or  hundred millions. It takes lots of time to extract. I want to use custom sql to get data which is needed like past 3 day's data, and the three columns: rpt-date(report date), number of records (each table), and geo cd.

       

      My questions:

       

      1. As to these 9 tables with reference table, should I use union all or join in order to get the three columns data: rpt_date, geo cd, and esp. the number of records for each table?

       

      2. Can I  use union all below to get number of records for each table?

       

      select rpt date, geo cd, count(*) from table1

      left join reference table on reference table.front_store_id = table1.front_store_id

      where rat date >= dated('day', -3, today())

      union  all

       

      select rpt date, geo cd, count(*) from table2

      left join reference table on reference table.front_store_id = table2.front_store_id

      where rat date >= dated('day', -3, today())union all

       

      selet rpt date, geo cd, count(*) from table 3

      left join reference table on reference table.front_store_id =o table3.front_store_id

      where rat date >= dated('day', -3, today())

      .....................etc.

       

      Please help! Any idea will be highly appreciated! Thanks in advance.

        • 1. Re: How to use custom sql to join or union all these tables
          Hari Ankem

          Can you provide some sample data (5-10 records) of 2 aggregated files and 2 detail files with corresponding data?

          By the way, the query you gave will not work since it is syntactically incorrect.

          • 2. Re: How to use custom sql to join or union all these tables
            minc650

            Hi Hari,

             

            Thank you for your reply. Sure, I'll provide sample workbook. I use the query as a sample. I also think it may not work. Thanks.

             

            Regards

             

            Nancy

            • 3. Re: How to use custom sql to join or union all these tables
              minc650

              The workbook is attached. Thanks.

              • 4. Re: How to use custom sql to join or union all these tables
                minc650

                I only use 6 tables here.

                • 5. Re: How to use custom sql to join or union all these tables
                  Hari Ankem

                  Correction: I don't see any reference data or detailed data in the attached workbook. So, am not sure what am supposed to do here.

                  Anyway, I see that the data is coming from Teradata. So, you can probably do the following:

                  1. Create a view in Teradata for each aggregated table to join with the reference table so that you have the location code too and then union all of them with common columns as required.

                  2. I presume the detailed data also exists in Teradata. So, in the above query itself you joined each aggregated table with the reference, see if you can join it with the detail table also.

                  • 6. Re: How to use custom sql to join or union all these tables
                    minc650

                    Hi Hari,

                     

                    Thank you for your solution. It is really appreciated that you spend your valuable time helping me. I'm not sure I can create any view or stored procedures in our Teradata database. Yes, the detail tables also exist in the database.

                    I also want to know how I can get number of records for each table by union all each table?

                    Thanks.

                     

                    Nancy

                    • 7. Re: How to use custom sql to join or union all these tables
                      Hari Ankem

                      You should be able to create a view. If you don't have the privileges, you can ask someone who does. It will simplify a lot of things for you. Don't create a stored procedure. I am giving you the syntax for the same. If you still cannot get a view created, use the SELECT query structure given below in Tableau Custom SQL and make changes as required.

                       

                      REPLACE VIEW database_name.view_name

                      (

                          field1, field2,

                          rpt_date, geo_cd,

                          dtl_field1, dtl_field2,

                          detail_count

                      )

                      AS LOCK ROW FOR ACCESS

                       

                       

                      SELECT  table1.field1 AS field1, table1.field2 AS field2,

                              ref_table.rpt_date AS rpt_date, ref_table.geo_cd AS geo_cd,

                              detail_table1.field1 AS dtl_field1, detail_table1.field2 AS dtl_field2,

                              detail_table1.COUNT(1) AS detail_count

                      FROM    database_name.table1,

                      LEFT JOIN database_name.ref_table

                          ON reference TABLE.front_store_id = table1.front_store_id

                      LEFT JOIN database_name.detail_table1

                          ON detail_table1.join_field1 = table1.join_field1     

                      WHERE rpt DATE >= CURRENT_DATE-3

                      GROUP BY 1,2,3,4,5,6

                       

                       

                      UNION  ALL

                       

                       

                      SELECT  table2.field1 AS field1, table2.field2 AS field2,

                              ref_table.rpt_date AS rpt_date, ref_table.geo_cd AS geo_cd,

                              detail_table2.field1 AS dtl_field1, detail_table2.field2 AS dtl_field2,

                              detail_table2.COUNT(1) AS detail_count

                      FROM    database_name.table2,

                      LEFT JOIN database_name.ref_table

                          ON reference TABLE.front_store_id = table2.front_store_id

                      LEFT JOIN database_name.detail_table2

                          ON detail_table2.join_field1 = table2.join_field1     

                      WHERE rpt DATE >= CURRENT_DATE-3

                      GROUP BY 1,2,3,4,5,6;

                      • 8. Re: How to use custom sql to join or union all these tables
                        minc650

                        Hi Hari,

                         

                        The query you provide is very helpful. I'll study the query, and see how it is going on in my work. Thank you for all the help!

                        Have a nice day!

                         

                        Regards

                         

                        Nancy