Subqueries inside NVL hive

Question

so i have to transfer data from oracle to hive. MY problem is there is NVL and 2 other subqueries inside the NVL.

   SELECT CCODE,
    E_DATE,
    E_HOUR,
    E_MINUTE,
    EXPI_DATE,
    MR_RATE,
    CMAR
FROM
  (SELECT CCODE,
    E_DATE,

 NVL(
    (SELECT MIN(DATE_TIME)AS E_DATE 
        FROM (SELECT CURR_H_C,
                    SEQUENCE,
                    DATE_TIME 
                        FROM (SELECT CURR_H_C,
                                SEQUENCE,
                                DATE_TIME,
                                    CASE
                                        WHEN lead(ed) over (partition BY CURR_H_C order by ed) IS NULL
                                        THEN 'Y'
                                        ELSE 'N'
                                        END flag
                                FROM table_one
                             WHERE SEQUENCE = '1')
                WHERE flag = 'Y') Z
     WHERE REGEXP_SUBSTR (CURR_H_C, '[^;]+', 1, 1) = A.CCODE
     AND Z.DATE_TIME                                        > A.DATE_TIME
    ),

    (SELECT MIN(DATE_TIME)AS E_DATE
        FROM (SELECT CRCY_CODE,
                    SEQUENCE,
                    DATE_TIME 
                    FROM(SELECT CRCY_CODE,
                            SEQUENCE,
                            DATE_TIME,
                                CASE
                                    WHEN lead(ed) over (partition BY CRCY_CODE order by ed) IS NULL
                                    THEN 'Y'
                                    ELSE 'N'
                                    END flag
                            FROM table_two
                            WHERE SEQUENCE = '1')
                            WHERE flag = 'Y') Z
                                WHERE CRCY_CODE = A.CCODE
                                AND Z.DATE_TIME     > A.DATE_TIME
                                )
                                 )AS EXPI_DATE,


                                M.MR_RATE,

How to translate it on hive? I already try IFNULL() and the other.

Any help/ideas will be appreciate, surely!