8 Replies Latest reply on Sep 29, 2018 12:21 PM by Sowmya Potluri

    SQL Starts with statement not working in tableau

    Sowmya Potluri

      Hi Tableau vizz,

       

      we have volatile data  and we need to pull the data into tableau by given sql starts with " with" statement  , attached the example and also i  read few blogs , where CTE SQLs are feasible to tableau... but i am not sure the workaround...

      can someone suggest me , what changes should i make to run SQL in Tableau 10.1 (teradata database ) appreciate your help.

      WITH ACSS_TRAN_CTE AS (

      SEL MRCH_NBR,MCC_CDE,TOT_TRAN_AMT,TRAN_CDE ,ACQ_ID ,MRCH_CNTRY_CDE FROM T_EDW1.ACSS_TRAN

      WHERE PROC_DT BETWEEN '2017-09-01' AND '2018-08-31'  AND TRAN_CDE IN('1720','1700')

      ),

      NNMD_CLKP_CTE AS  (

      SEL DISTINCT NNMD.ACQ_ID,NOVUS_MRCH_NBR  FROM

      (

      SEL NOVUS_MRCH_NBR , ACQ_ID FROM T_MDSS.NNMD_CLKP

      )NNMD

      )

      and i had few more sql, where it start with select.

       

       

      Thanks,sowmya

        • 1. Re: SQL Starts with statement not working in tableau
          Mahfooj Khan

          Hi,

           

          Simplest approach would be create a view or table of your SQL statements then connect that view/table in tableau.

          Or instead of abbreviation you need to use proper syntax of the keywords used in SQL statements. Like SEL should be SELECT etc.

          Let us know if this works.

           

          Mahfooj

          • 2. Re: SQL Starts with statement not working in tableau
            Wanda Wang

            Hi Sowmya,

             

            CTE or temporary tables are not supported by Tableau.

             

            As an alternative, you can either create a view or table of your SQL statements and connect to that view/table as suggested by Mahfooj, or writing longer queries with join statements that don't involve CTE.

             

            Hope that helps.

             

            Wanda

            • 3. Re: SQL Starts with statement not working in tableau
              Robert Janezic

              Hello Sowmya,

               

              If you want to use a CTE, pleas refer to this https://kb.tableau.com/articles/HowTo/using-common-table-expressions

              1 of 1 people found this helpful
              • 4. Re: SQL Starts with statement not working in tableau
                Sowmya Potluri

                Hi Mahfooj, thanks for the response... but my team not ready to create view due to spool space , as we are trying to pull billions of data (drill down Data)(  CTE sql  itself union of 26 select sqls, Where it started with statment .) let me know is there any other posibiltiess... if not i will update my team.

                 

                Thanks and regrads,

                sowmya

                • 5. Re: SQL Starts with statement not working in tableau
                  Sowmya Potluri

                  Hello Robert,

                  thanks for the link, i looked into it.. i tried to give with SQL statment  in Initial SQL , but how do it connect to Custom SQL... (select SQL)... can someone assist me .

                  something going wrong and i am not able to figure it out.

                  let me know, what exactly should i give in  initial SQL and where should i break my original SQL to keep the other part in Custom SQL .

                   

                  sample of my SQL

                  WITH ACSS_TRAN_CTE AS (

                  SEL MRCH_NBR,MCC_CDE,TOT_TRAN_AMT,TRAN_CDE ,ACQ_ID ,MRCH_CNTRY_CDE FROM T_EDW1.ACSS_TRAN

                  WHERE PROC_DT BETWEEN '2017-09-01' AND '2018-08-31'  AND TRAN_CDE IN('1720','1700')

                  ),

                  NNMD_CLKP_CTE AS  (

                  SEL DISTINCT NNMD.ACQ_ID,NOVUS_MRCH_NBR  FROM

                  (

                  SEL NOVUS_MRCH_NBR , ACQ_ID FROM T_MDSS.NNMD_CLKP

                  )NNMD

                  )

                   

                  SEL

                  cast ('Foreign financial institutions, including banks and foreign money services providers' AS VARCHAR(300) ) AS BUS_INDST_TYP_TXT       ,     

                  1. ACSS.MRCH_NBR,

                  ACSS.MCC_CDE,

                  cast ('1' AS VARCHAR(20)) AS DSHBRD_TYP                    ,

                  cast ('DN' AS VARCHAR(15)) AS LOB,

                             

                        Sum(CASE WHEN ACSS.TRAN_CDE IN('1700','1720')

                                 THEN Cast(ACSS.TOT_TRAN_AMT  AS DECIMAL(20,4))

                                 ELSE 0.00

                            END) AS TRAN_VOL_AMT,

                      '2018-08-31' AS proc_dt   ,                

                  '2018-08-31' AS trnsfrm_dt

                   

                      FROM

                  (

                   

                  SEL MRCH_NBR,MCC_CDE,TOT_TRAN_AMT,TRAN_CDE ,ACQ_ID,MRCH_CNTRY_CDE FROM ACSS_TRAN_CTE

                  WHERE  MCC_CDE IN ('6010', '6011' )

                  AND Character_Length(Trim(MRCH_CNTRY_CDE)) > '2'

                   

                  )ACSS

                  INNER JOIN

                  (

                  SEL DISTINCT NOVUS_MRCH_NBR FROM  NNMD_CLKP_CTE NNMD

                  INNER JOIN

                  (SEL  ecr.PRT_NBR ,ecr.ACQ_ID

                  FROM

                  T_EDW4.ECREDIT_PRT_TRNSL_SNPSHT  ecr,

                    T_EDW4.ECREDIT_PRT_DTL_DLY  d  ,

                    T_EDW4.ECREDIT_PRT_TYP_MP_DLY mp_dly

                    WHERE d.PRT_NBR = ecr.PRT_NBR AND mp_dly.PRT_NBR=ECR.prt_nbr

                    AND mp_dly.PRT_CUST_TYP_ID IN ('2','3')

                  /*AND d.PRT_ACCT_STAT_DSC  <> 'Inactive'*/

                    AND ecr.EXP_DT = '9999-12-31'

                    /*AND d.EXP_DT = '9999-12-31'*/

                  /*AND mp_dly.EXP_DT = '9999-12-31'*/

                  AND d.PRT_DMCL_CNTRY_CDE  <> 'CA'

                  AND d.PRT_DMCL_CNTRY_CDE <> 'US' 

                  QUALIFY Row_Number()

                  Over(PARTITION BY  ecr.ACQ_ID

                  ORDER BY d.EXP_DT DESC, mp_dly.EXP_DT   DESC  ,CASE WHEN d.PRT_ACCT_STAT_DSC <> 'INACTIVE' THEN 0 ELSE 1 END

                  ) =1

                  /*GROUP BY 1,2*/

                   

                  )SNP

                  ON NNMD.ACQ_ID= SNP.ACQ_ID

                  )T1

                  ON T1.NOVUS_MRCH_NBR =ACSS.MRCH_NBR

                  GROUP BY 1 ,2,3

                   

                  UNION

                   

                  SEL

                  'Nonbank financial Institutions' AS BUS_INDST_TYP_TXT       ,     

                  1. ACSS.MRCH_NBR,

                  ACSS.MCC_CDE,

                  '1' AS DSHBRD_TYP                    ,

                  'DN' AS LOB,

                           

                        Sum(CASE WHEN ACSS.TRAN_CDE IN('1700','1720')

                                 THEN Cast(ACSS.TOT_TRAN_AMT  AS DECIMAL(20,4))

                                 ELSE 0.00

                            END) AS TRAN_VOL_AMT,

                      '2018-08-31' AS proc_dt   ,                

                  '2018-08-31' AS trnsfrm_dt

                   

                      FROM

                  (

                   

                  SEL MRCH_NBR,MCC_CDE,TOT_TRAN_AMT,TRAN_CDE ,ACQ_ID FROM  ACSS_TRAN_CTE

                  WHERE MCC_CDE IN

                  ('3551','3555','3560','3564','3597','3624','3628','3662','3667','3669','3676','3679','3682','3708','3712','3728','3731','3737','3738','3761','3762','3764','3766','3767

                  ','3768','3769','3771','3773','3774','3792','3794','5094','5271','5271','5511','5511','5521','5521','5551','5551','5561','5561','5571','5571','5592','5592','5598','5598'

                  ,'5944','5971','5971','5994','5994','6211','6211','6531','7538','7538','7631','7801')

                  /*AND MRCH_CNTRY_CDE <> 'US'*/

                   

                  )ACSS

                  INNER JOIN

                  (

                  SEL DISTINCT NOVUS_MRCH_NBR FROM  NNMD_CLKP_CTE NNMD

                  INNER JOIN

                  (SEL  ecr.PRT_NBR ,ecr.ACQ_ID

                  FROM

                  T_EDW4.ECREDIT_PRT_TRNSL_SNPSHT  ecr,

                    T_EDW4.ECREDIT_PRT_DTL_DLY  d  ,

                    T_EDW4.ECREDIT_PRT_TYP_MP_DLY mp_dly

                    WHERE d.PRT_NBR = ecr.PRT_NBR AND mp_dly.PRT_NBR=ECR.prt_nbr

                    AND mp_dly.PRT_CUST_TYP_ID IN ('2','3')

                  /*AND d.PRT_ACCT_STAT_DSC  <> 'Inactive'*/

                    AND ecr.EXP_DT = '9999-12-31'

                    /*AND d.EXP_DT = '9999-12-31'*/

                  /*AND mp_dly.EXP_DT = '9999-12-31'*/

                  AND d.PRT_DMCL_CNTRY_CDE  <> 'CA' 

                  QUALIFY Row_Number()

                  Over(PARTITION BY  ecr.ACQ_ID

                  ORDER BY d.EXP_DT DESC, mp_dly.EXP_DT   DESC  ,CASE WHEN d.PRT_ACCT_STAT_DSC <> 'INACTIVE' THEN 0 ELSE 1 END

                  ) =1

                  /*GROUP BY 1,2*/

                   

                  )SNP

                  ON NNMD.ACQ_ID= SNP.ACQ_ID

                  )T1

                  ON T1.NOVUS_MRCH_NBR =ACSS.MRCH_NBR

                  GROUP BY 1 ,2,3

                  ...........................................................................................................................................

                  ..................................................................................

                  .......................................................26 SQLS Union .................

                  .......................................................

                  ......................................

                  ORDER BY d.EXP_DT DESC, mp_dly.EXP_DT   DESC  ,CASE WHEN d.PRT_ACCT_STAT_DSC <> 'INACTIVE' THEN 0 ELSE 1 END

                  ) =1

                  /*GROUP BY 1,2*/

                   

                  )SNP

                  ON NNMD.ACQ_ID= SNP.ACQ_ID

                  )T1

                  ON T1.NOVUS_MRCH_NBR =ACSS.MRCH_NBR

                  GROUP BY 1 ,2,3

                   

                  Appreciate your help!!

                   

                  Regards,sowmya

                  • 6. Re: SQL Starts with statement not working in tableau
                    Robert Janezic

                    Sowmya,

                     

                    Hopefully this will help!

                    • 7. Re: SQL Starts with statement not working in tableau
                      Sowmya Potluri

                      Thanks Robert! Will keep post if it works

                       

                      On Wed, Sep 26, 2018 at 8:19 AM Robert Janezic <tableaucommunity@tableau.com>

                      • 8. Re: SQL Starts with statement not working in tableau
                        Sowmya Potluri

                        Hello Robert,

                         

                        Sql In Intial SQL itself execting more than 300 min... i am not sure  what the workaround...

                         

                        create table s_btgrp.ACSS_TRAN_CTE AS (

                        SEL MRCH_NBR,MCC_CDE,TOT_TRAN_AMT,TRAN_CDE ,ACQ_ID ,MRCH_CNTRY_CDE FROM T_EDW1.ACSS_TRAN

                        WHERE PROC_DT BETWEEN '2017-09-01' AND '2018-08-31'  AND TRAN_CDE IN('1720','1700')

                        ) with data

                        any idea, why it taking long time

                         

                         

                        Regards,sowmya