how to derive filter condition from table rows

How do i derive the filter condition in bteq based on rows like given below?

TABLE B:

COLA COLB COLC
1    CODE  AAA
1    DESC  BBB
1    TYPE  CCC

Here is some code:

SELECT * FROM TABLE A WHERE CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC')

One approach here would be to aggregate by COLA, and then add three assertions for the key/values you expect:

SELECT
    COLA
FROM yourTable
GROUP BY
    COLA
HAVING
    COUNT(CASE WHEN COLB = 'CODE' AND COLC = 'AAA' THEN 1 END) > 0 AND
    COUNT(CASE WHEN COLB = 'DESC' AND COLC = 'BBB' THEN 1 END) > 0 AND
    COUNT(CASE WHEN COLB = 'TYPE' AND COLC = 'CCC' THEN 1 END) > 0;

You can re-write query using common table expression.

WITH CTE_TableB(ColA, Code, DESC,TYPE) AS
(
SELECT COLA, CASE WHEN COLB = 'Code' THEN COLC ELSE NULL AS Code
           , CASE WHEN COLB = 'DESC' THEN COLC ELSE NULL AS DESC
           , CASE WHEN COLB = 'TYPE' THEN COLC ELSE NULL AS TYPE
FROM TABLEB
)
SELECT *
FROM CTE_TableB
WHERE  CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC');

Or using derived table

SELECT *
FROM
(
SELECT COLA, CASE WHEN COLB = 'Code' THEN COLC ELSE NULL AS Code
           , CASE WHEN COLB = 'DESC' THEN COLC ELSE NULL AS DESC
           , CASE WHEN COLB = 'TYPE' THEN COLC ELSE NULL AS TYPE
FROM TABLEB
) AS t
WHERE  CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC');

To filter table A using the values in table B, and assuming that a column without values in table B means unfiltered on that column, you can do it like this:

SELECT *
  FROM A
 WHERE ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'CODE' )
      OR A.CODE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'CODE' ) )
   AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'DESC' )
      OR A.DESC IN ( SELECT B.COLC FROM B WHERE B.COLB = 'DESC' ) )
   AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'TYPE' )
      OR A.TYPE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'TYPE' ) )

You can use in or exists for this purpose:

select a.*
from a 
where a.code in (select b.colc from b where b.colb = 'CODE') and
      a.desc in (select b.colc from b where b.colb = 'DESC') and
      a.type in (select b.colc from b where b.colb = 'TYPE');

The one caveat is that this assumes that there is at least one of each value in table b.

You can handle missing values. One method uses additional subqueries:

select a.*
from a 
where (a.code in (select b.colc from b where b.colb = 'CODE') or
       not exists (select 1 from b where b.colb = 'CODE')
      ) and
      (a.desc in (select b.colc from b where b.colb = 'DESC') or
       not exists (select 1 from b where b.colb = 'DESC')
      ) and
      (a.type in (select b.colc from b where b.colb = 'TYPE') or
       not exists (select 1 from b where b.colb = 'TYPE')
      );