Trying to filter on a union of 3 queries-CodePudding

I have a union of 3 queries that summarizes like this...

Select param1 As 'example1' And .... Where... 

Union All

Select param1 As 'example2' And .... Where...

Union All

Select param1 As 'example3' And .... Where...

Is there any way to wrap this in a Select and create an optional parameter that filters on example1/example2/example3?

Any help would be greatly appreciated. Thanks!

CodePudding user response：

Is there any way to wrap this in a Select and create an optional parameter that filters on example1/example2/example3?

Assuming that I understood your need then you can use common table expression (CTE).

In the following document you can read more about the option of using CTE: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

In general, you wrap your code into a CTE (in the following example I will name my CTE MyCTE but you can use any name), and then in the outside query you can add the filter which you want.

Do not confuse between CTE and a variable or temporary table. A CTE is not a physical entity but logically for the sake pf the query and the server will parse it as inline code.

For example:

;With MyCTE as ( <here comes the code you want to wrap> )
SELECT <choose columns to select> 
FROM MyCTE 
<here you can add order by or filetring with where>

In your case it might look like:

;With MyCTE as (
  -- when you use UNION then the name of the columns in the result is configured by the names in the first query, so you do not need all the "as..." for the other queries.
  -- not clear why you use "And" in the name of the columns you select. If you need more than one column then you should use comma "," between the columns
  Select param1 As [example1], column2, column3... Where... <this filter only this specific query>
  Union All
  Select param1, column2, column3.... Where...
  Union All
  Select param1, column2, column3.... Where...
)
SELECT param1, column2, column3....
FROM MyCTE -- we select from the logical CTE as it was a table
WHERE <here you can add condition to filter the result of the UNION>

CodePudding user response：

Yes it is possible. And here is where you should make the effort provide consumable information that you and everyone else can use as a basis for a solution. Below is the code in this fiddle which you can play with.

-- "parameters"
--declare @p1 varchar(20) = null;
declare @p1 varchar(20) = 'waggle';

-- some crap data
declare @x table (id int, trandate date); 
insert @x (id, trandate) values
(1, '20190101'), (2, '20190425'), 
(4, '20200311'), (5, '20200630'), (11, '20200801'), 
(12, '20210101'), (13, '20210710');

with crap_union as (
   select 'wiggle' as [when], id, trandate 
       from @x where year(trandate) = 2019
   union all 
   select 'waggle' as [when], id, trandate 
       from @x where year(trandate) = 2020
   union all 
   select 'waddle' as [when], id, trandate 
       from @x where year(trandate) = 2021
)
select * from crap_union
where [when] = @p1 or @p1 is null 
order by [when], id, trandate
;

Same idea as generically expressed by Ronen. You add some sort of identifier to each query in the UNION. That allows you to "see" which row comes from which query. You stuff that UNION into a CTE or a derived table (same logical effect) and then select from that CTE (or derived table) with the desired filter.

You might consider reading Erland's discussion on dynamic search conditions to understand the performance considerations of this approach. Bookmark Erland's website since it has much useful information.