CREATE VIEW
    V_VIEW
    (
        COL1, COL2, COL3, COL4
    ) AS
SELECT
    "COL1", "COL2", "COL3", "COL4"
FROM
    TABLE1
UNION ALL
SELECT
    "COL1", "COL2", "COL3", "COL4"
FROM
    TABLE2;

SELECT
        COL1, COL2
FROM
    (   SELECT
            COL1, COL2
        FROM
            V_VIEW
        WHERE
            COL1 like 'val%'
        AND COL2 =
            (
                SELECT
                    MAX(COL3)
                FROM
                    V_VIEW
                WHERE
                    COL4 = 'Y' ) part1
UNION ALL
SELECT
        COL1, COL2
FROM
    (   SELECT
            COL1, COL2
        FROM
            V_VIEW
        WHERE
            COL1 like 'sth%'
        AND COL2 =
            (
                SELECT
                    MIN(COL3)
                FROM
                    V_VIEW
                WHERE
                    COL4 = 'N' ) part2;

WITH TEMP_TABLE AS (
SELECT
    COL1, COL2, COL3, COL4
FROM
    TABLE1
UNION ALL
SELECT
    COL1, COL2, COL3, COL4
FROM
    TABLE2 )
SELECT
        COL1, COL2
FROM
    (   SELECT
            COL1, COL2
        FROM
            TEMP_TABLE
        WHERE
            COL1 like 'val%'
        AND COL2 =
            (
                SELECT
                    MAX(COL3)
                FROM
                    TEMP_TABLE
                WHERE
                    COL4 = 'Y' ) part1
UNION ALL
SELECT
        COL1, COL2
FROM
    (   SELECT
            COL1, COL2
        FROM
            TEMP_TABLE
        WHERE
            COL1 like 'sth%'
        AND COL2 =
            (
                SELECT
                    MIN(COL3)
                FROM
                    TEMP_TABLE
                WHERE
                    COL4 = 'N' ) part2