SQL Query Assistance With Union and Using Singular Where Clause

I have a query that is unioned. Initially, my only where clause had to do with a product table that both unions used so I was able to use this where clause which covered the entire query

With Filter_Product_R As
(
Select
D.*,
Case
When SUBSTR(D.PRODID,1,5) ='00000'
Then SUBSTR(D.PRODID,6,7)
Else D.PRODID
End As Product_ID3
From
XXXX.PRODUCT_R as D
Where
D.PROD_MODEL in('QD','QS','COR','GU','IP','TH OTHER','LABS','BURST','NET','DATA,'ON')
OR
(
D.BRAND IN ('QD','QS','COR','GU','IP','TH OTHER','LABS','BURST','NET','DATA,'ON')
OR
(
SUBSTR(D.PRODID,1,4) IN ('3562','3563','3564''3564','9235',
'9231','7199','7198','4195','7993','4378','4379')
AND D.DIV IN ('13','2D','Z3','Z1')
)
OR
(
SUBSTR(D.PRODID,6,4) IN ('3562','3563','3564','9235',
'9231','7199','7198','4195','7993','7994','4378','4 379')
AND D.DIV IN ('13','2D','Z3','Z1')
)
OR SUBSTR(D.PRODID,1,4) IN ('2462') AND D.DIV = '26'
OR SUBSTR(D.PRODID,6,4) IN ('2462') AND D.DIV = '26'
OR D.PRODID = '7A2DNZZ'
)
)

Now I need to add an "A" reference as part of my where clause

The A is the exact same column in both unioned instances meaning I need to add a

It may be better for performance reasons to apply the shared filtering conditions to each half of the union. On the other hand, the compiler may make good choices regardless.

If you need to treat rows in a result set differently based on the source, create a separate column to identify the source. This is pretty simple with UNION ALL (which is more eficient unless there is a possibility of duplicates between or within source sets and you want to eliminate them).

select ....., 'A' as row_source from XXXX.ORDER as A
union all
select ....., 'D' as row_source from XXXX.PRODUCT as D
where ....
OR (row_source = 'A' and MA like '3%')

With UNION instead of UNION ALL, it gets a little trickier. The different values for row_source would prevent the elimination of duplicates. There are a number of ways to address this, but I'd probably use the approach above then apply DISTINCT on the result set. Since row_source would be used for filtering only but not be included in the results set, duplicates would be eliminated.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.