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



      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]
            ,r.CountryName as Country
      (SELECT po.[PreOrderCode]
         , (case 
            when po.BrandId in ('1')  and po.PreOrderStatusId in ('0','1')
        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
        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))
      (MID([Pre Order Details],(FIND( [Pre Order Details],'ShippingCountryIso":"') + LEN('ShippingCountryIso":"')),2))




      Any suggestion is highly appreciated.