2 Replies Latest reply on Aug 9, 2018 1:47 PM by Andrew Schrag

    Using with clause in Custom sql have issue

    Sankar Ranganathan

      HI ,

       

      I am connecting hadoop table to generate the report for the following query, it's working fine directly in hive  but not working in tableau report query and am greeting the following error message.

       

      "[Hortonworks][HiveODBC] (80) Syntax or semantic analysis error thrown in server while execurint query. Error message from server: Error while compiling statement: FAILED: ParseException line 3:7 cannot recognize input near 'WITH' 'Q1' 'AS' in from source"

       

      Sql Query:

       

      WITH Q1 AS

      (

      select CONCAT(SUBSTR(invc_dt,1,4),SUBSTR(invc_dt,6,2)) as dist_month

      from

      MVR_CPS_BILL_RNWL

      group by CONCAT(SUBSTR(invc_dt,1,4),SUBSTR(invc_dt,6,2))

      )

      select t2.month as File_Creation_Month,t1.business_segment as Business_Segment,

      t1.driver_license_state_cd as State_Code,t2.total_mvrs as Total_MVRs,

      t1.drv_cnt as Driver_Count,

      CASE WHEN SUM (t1.drv_cnt) IS NULL OR SUM (t1.drv_cnt) = 0 THEN 0

              ELSE round((SUM(total_mvrs) / SUM(t1.drv_cnt))*100,2)

               END as MVR_Order_Rate_PCT

      from Q1,

      (select concat(substr(plcy_exdt,1,4),substr(plcy_exdt,6,2)) as month,

      driver_license_st_cd as driver_license_state_cd,

      business_segment_org_id as business_segment,

      count(distinct driver_license_nbr) as drv_cnt from

      .MVR_PLDW_DLYREPORT_RNWL

      where driver_license_st_cd <>  'null' 

      AND concat(substr(plcy_exdt,1,4),substr(plcy_exdt,6,2)) >= '201701'

      group by concat(substr(plcy_exdt,1,4),substr(plcy_exdt,6,2)) ,

      business_segment_org_id,

      driver_license_st_cd)t1

      INNER JOIN

      (

      select CONCAT(SUBSTR(TRANS_dt,1,4),SUBSTR(trans_dt,6,2)) as month,

      segment  AS bus_seg_org_id,

      state_cd as state,count(first_nm) as total_mvrs from

      MVR_CPS_BILL_RNWL

      where concat(substr(trans_dt,1,4),substr(trans_dt,6,2)) >= '201701'

      and prod ='MVR'

      and rpt_nm NOT IN ('MVR AZ12','MVR AZ12 CUS','MVR CA12','MVR MN MTHSTDB','MVR SC12')

      group by CONCAT(SUBSTR(trans_dt,1,4),SUBSTR(trans_dt,6,2)),

      segment,state_cd

      )t2

      ON t1.business_segment=t2.bus_seg_org_id and

      t1.driver_license_state_cd=t2.state and

      t1.month= concat(substr(add_months(to_date(from_unixtime(UNIX_TIMESTAMP(Q1.dist_month,'yyyyMM'))),5),1,4),

      substr(add_months(to_date(from_unixtime(UNIX_TIMESTAMP(Q1.dist_month,'yyyyMM'))),5),6,2))

      and

      t2.month=Q1.dist_month

      and Q1.dist_month > 201801

      group by t2.month,t1.business_segment,t1.driver_license_state_cd,

      t2.total_mvrs,t1.drv_cnt

        • 1. Re: Using with clause in Custom sql have issue
          nikher verma

          Hi Sankar Ranganathan ,

           

          There's slight difference between Normal SQL Query and  tableau's custom SQL Query .

          You have to check  documentation to understand what cant be done in Tableau's custom SQL Query .

          Few links are ::

           

          Tableau – Using SQL queries to get data – Techno Whisp

           

          Stored Procedures

           

          Please follow this thread :

          Using WITH in SQL query in Tableau

           

          The reason you can't use Common Table Expressions (i.e. the WITH clause) in Tableau's custom SQL connections is that a Common Table Expression has to be defined at the outermost level of the query. You say something like:

           

          WITH (CTE)

          (rest of the SQL statement, which references the CTE)

           

          That doesn't work in a custom SQL connection because the custom SQL expression actually becomes a sub-query in a larger statement constructed by Tableau.

           

          For the same reason you can't use SQL Server "Query Hints" in a custom SQL connection (since they have to be defined at the outermost level), but you can use "Table Hints" (which are embedded within the query).

           

           

          -Nikher Verma

          • 2. Re: Using with clause in Custom sql have issue
            Andrew Schrag

            This is certainly a bummer as it is a useful design for queries.

             

            What I find strange is the reason makes it sound like this would be true for all custom queries, but it is not. CTEs do not work with SQL Server, but they DO work with a Vertica connection, for example.