Conditionally selecting which table to pull from

Hi guys,

I'm having trouble finding answers on how to conditionally pull from a specific table, i.e. "select dimension, sum(metric1), sum(metric2) from case when dimension = '123' then [view1] else [view2] end group by dimension".  Any ideas?  Happy to clarify the question more if needed.

Thanks!

Brien

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Brien Jones ! Definitely an interesting question, case when statements cannot go into the from clause, but I wonder if your set up would fit into something with a union all... like so:

    select
    dimension
    , sum(metric1)
    , sum(metric2)
    from [view1]
    where dimension = '123'
    group by dimension
    union all
    select
    dimension
    , sum(metric1)
    , sum(metric2)
    from [view2]
    where dimension != '123'
    group by dimension

     

    Let me know if that would work for you here!

    Reply Like
      • Brien Jones
      • Dr. Jones
      • Brien_Jones
      • 6 mths ago
      • Reported - view

      Neha Kumar Hey Neha, thanks!  I think that will not handle the use case I'm addressing because I want to conditionally pull from different tables to reduce run times.  Like if data set 1 is filtered to then pull from data table 1 and if data set 2 is filtered to then pull from table 2, etc.

      Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 6 mths ago
      • Reported - view

      Brien Jones If the two tables have the same schema, you can set a filter that has the name of the table. Then the user can select which table they want to query via the filtering UI. Let's say this filter is called table_name

      The query will then look like this:

      select
      dimension
      , sum(metric1)
      , sum(metric2)
      from [table_name]
      group by dimension

      I wish SQL allowed more flexibility with their case whens (similar to something like Python or R!), but the solution here would either need to get creative with the union alls or to use Periscope filters within your query. Hope this helps 😀

      Reply Like
      • Brien Jones
      • Dr. Jones
      • Brien_Jones
      • 6 mths ago
      • 1
      • Reported - view

      Neha Kumar Hey, this provides me with a few options to explore.  Thank you so much!

      Reply Like 1
Like Follow
  • 6 mths agoLast active
  • 4Replies
  • 88Views
  • 2 Following