Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE SRC_TBL (TaxCode, ExemCode, CountryCode) AS
SELECT 'TX1','EX1','US' FROM DUAL UNION ALL
SELECT 'TX1','EX2','UK' FROM DUAL UNION ALL
SELECT 'TX3','EX1','US' FROM DUAL UNION ALL
SELECT 'TX2','EX2','UK' FROM DUAL;
4 rows affected

CREATE TABLE SPEC_TBL_TX(TaxCode) AS
SELECT 'TX1' FROM DUAL UNION ALL
SELECT 'TX2' FROM DUAL UNION ALL
SELECT 'TX4' FROM DUAL;
3 rows affected

CREATE TABLE SPEC_TBL_EX(ExmCode) AS
SELECT 'EX1' FROM DUAL UNION ALL
SELECT 'EX3' FROM DUAL UNION ALL
SELECT 'EX4' FROM DUAL;
3 rows affected

CREATE TABLE SPEC_TBL_CTY(Country) AS
SELECT 'US' FROM DUAL UNION ALL
SELECT 'IN' FROM DUAL UNION ALL
SELECT 'SG' FROM DUAL;
3 rows affected

SELECT s.*,
       COALESCE(
         TRIM(
           TRAILING '+' FROM
           CASE
           WHEN NOT EXISTS(SELECT 1
                           FROM   spec_tbl_tx t
                           WHERE t.taxcode = s.taxcode)
           THEN 'Invalid taxcode+'
           END
           || CASE
           WHEN NOT EXISTS(SELECT 1
                           FROM   spec_tbl_ex e
                           WHERE  e.exmcode = s.exemcode)
           THEN 'Invalid exemcode+'
           END
           || CASE
           WHEN NOT EXISTS(SELECT 1
                           FROM   spec_tbl_cty c
                           WHERE  c.country = s.countrycode)
           THEN 'Invalid countrycode+'
           END
         ),
         'VALID'
       ) AS error
FROM   src_tbl s


TAXCODE
EXEMCODE
COUNTRYCODE
ERROR


TX1
EX1
US
VALID

TX1
EX2
UK
Invalid exemcode+Invalid countrycode

TX3
EX1
US
Invalid taxcode

TX2
EX2
UK
Invalid exemcode+Invalid countrycode

TAXCODE	EXEMCODE	COUNTRYCODE	ERROR
TX1	EX1	US	VALID
TX1	EX2	UK	Invalid exemcode+Invalid countrycode
TX3	EX1	US	Invalid taxcode
TX2	EX2	UK	Invalid exemcode+Invalid countrycode