3 Replies Latest reply on Aug 19, 2016 7:15 AM by Andrew Watson

    Is it possible to create nested SQL queries in Data Connection canvas using calculated field?

    Mohit Sharma

      Hi,

       

      I am currently extracting data from SQL database using a custom SQL query which has a nested query in it. Now as we know its always better better to avoid custom sql in tableau from performance point of view, I was wondering how to develop the data model as I have in my custom SQL on the data canvas of Tableau. I can easily put the joins and all, the only issue arises when I have to configure the nested query which has a calculated field in it.

       

      Below is a sample query I used in my custom sql:

       

       

       

      SELECT x.[PreOrderCode]
            ,x.[BrandName]
            ,x.[CreationTime]
            ,x.[LastUpdateTime]
            ,r.CountryName as Country
      from
      
      
      (SELECT po.[PreOrderCode]
            ,b.[BrandName]
            ,po.[CreationTime]
            ,po.[LastUpdateTime]
         , (case 
            when po.BrandId in ('1')  and po.PreOrderStatusId in ('0','1')
                     then 
           (SUBSTRING(po.PreOrderDetails,
        CHARINDEX('ShippingCountryIso":"', po.PreOrderDetails) + LEN('ShippingCountryIso":"'),2)) COLLATE DATABASE_DEFAULT
      
        end) as BillingCountry
      
      
        FROM [PreOrder] po
         LEFT JOIN Orders o
         ON po.OrderNumber = o.BrandOrderReference
         LEFT JOIN NameAddresses n
         ON o.ConsigneePartyId = n.NameAddressId
         LEFT JOIN  Payments p
         ON o.PaymentId = p.PaymentId
         LEFT JOIN Countries c
         ON n.CountryIso = c.Iso
         LEFT JOIN  Brands b
         ON b.BrandId = po.BrandId
      
      
          where  
        po.[CreationTime] > '2016-03-31 23:59:59' and 
        po.PreOrderStatusId in ('0','1'))x
         
         LEFT JOIN [Countries] r
                        ON  x.BillingCountry = r.Iso
      

       

       

      When I tried to configure the CASE statement in the nested query, I couldn't find a way for it. I have prepared an equivalent Tableau Formula for it, but have no idea how to use it efficiently. I tried creating a calculated field with below formula and putting it on filter shelf. But I think that will only decrease the performance. I am interested in Extract filters rather than normal/context filters.

       

      Tableau Equivalent Formula for CASE statement in above SQL:

       

      IF (([Brand Id] = 1)  AND  ([Pre Order Status Id] = 0 OR [Pre Order Status Id] = 1))
      THEN
      (MID([Pre Order Details],(FIND( [Pre Order Details],'ShippingCountryIso":"') + LEN('ShippingCountryIso":"')),2))
      END
      

       

       

       

      Any suggestion is highly appreciated.

       

      Thanks,

      M