Replace a column value with multiple values from another table - DB2

Question

I have a complex query ((Q1 union Q2) intersect (Q3 union Q4)). Now the issue is one of the column involved in the queries has the value of 'Company'. This value can be a particular company name or a value of 'ALL' which means all companies. I have a separate company table which lists the company names. Now the logic is if one of the query in intersect has a value of 'ALL' and the other query is company 'C1', the result should return 'C1'. But currently since 'ALL' does not match 'C1' as a string value the intersect does not return anything. Is there anyway I can replace the 'ALL' row value with all the company names and then do the intersect? I want to do all the operations through SQL because handling it in code will be very cumbersome. I am using DashDB which is based on DB2.

Answer 1

This is the query that solved my issue (I have removed complexities from the query and only included those portions related to this question)

SELECT 
AA.OBJECT_TYPE, 
AA.OBJECT_FILTER_ID, 
CASE WHEN BB.OBJECT_FILTER_VALUE = 'ALL' 
  THEN AA.OBJECT_FILTER_VALUE 
  ELSE BB.OBJECT_FILTER_VALUE 
  END AS OBJECT_FILTER_VALUE FROM 
( 
    SELECT 
    OBJECT_TYPE, 
    OBJECT_FILTER_ID, 
    OBJECT_FILTER_VALUE 
    from COMPANY
) AS AA 
INNER JOIN 
( 
    SELECT 
    OBJECT_TYPE ,
    OBJECT_FILTER_ID ,
    OBJECT_FILTER_VALUE 
    FROM DETAILS
) AS BB 
ON 
AA.OBJECT_TYPE = BB.OBJECT_TYPE AND 
AA.OBJECT_FILTER_ID = BB.OBJECT_FILTER_ID AND
(
AA.OBJECT_FILTER_VALUE = BB.OBJECT_FILTER_VALUE OR 
AA.OBJECT_FILTER_VALUE = 'ALL' OR 
BB.OBJECT_FILTER_VALUE = 'ALL'
)

Answer 2

That should work:

select
    company_name
    ,the
    ,other
    ,columns
from 
(
    select distinct
        company_name = ca.company_name
        ,the
        ,other
        ,columns
    from Q1
        inner join companies_table ca
            on Q1.company_name = ca.company_name
            or Q1.company_name = 'ALL'

    union

    select distinct
        company_name = ca.company_name
        ,the
        ,other
        ,columns
    from Q2
        inner join companies_table ca
            on Q2.company_name = ca.company_name
            or Q2.company_name = 'ALL'
) a

intersect

select
    company_name
    ,the
    ,other
    ,columns
from 
(
    select distinct
        company_name = ca.company_name
        ,the
        ,other
        ,columns
    from Q3
        inner join companies_table ca
            on Q3.company_name = ca.company_name
            or Q3.company_name = 'ALL'

    union

    select distinct
        company_name = ca.company_name
        ,the
        ,other
        ,columns
    from Q4
        inner join companies_table ca
            on Q4.company_name = ca.company_name
            or Q4.company_name = 'ALL'
) b

Answer 3

You can simplify your query like this:

SELECT AA.OBJECT_TYPE, AA.OBJECT_FILTER_ID, 
CASE WHEN BB.OBJECT_FILTER_VALUE = 'ALL' THEN AA.OBJECT_FILTER_VALUE ELSE BB.OBJECT_FILTER_VALUE END AS OBJECT_FILTER_VALUE 
FROM COMPANY AS AA 
INNER JOIN DETAILS AS BB ON (AA.OBJECT_TYPE, AA.OBJECT_FILTER_ID)=(BB.OBJECT_TYPE, BB.OBJECT_FILTER_ID) 
AND(AA.OBJECT_FILTER_VALUE = BB.OBJECT_FILTER_VALUE OR AA.OBJECT_FILTER_VALUE = 'ALL' OR BB.OBJECT_FILTER_VALUE = 'ALL')