use NVL (),() AS COLUMN IN HIVE

Question

I have to translate my PL/SQL data into HIVE with main format:

NVL(SELECT FROM (SELECT)),(SELECT FROM (SELECT)) AS EXPI_DATE

My raw data looks like this :

NVL(
(SELECT MIN(DATE_TIME)AS EF_DATE
    FROM (SELECT C,
                  S,
                  DATE_TIME FROM(SELECT C,
                                      S,
                                      DATE_TIME,
                                      CASE
                                      WHEN lead over ( order by ) IS NULL
                                        THEN 'Y'
                                        ELSE 'N'
                                      END AS flag
                                        FROM table_1 
                            )aihiscur
          WHERE flag = 'Y') Z
          AND Z.DATE_TIME                                        > A.DATE_TIME
),
(SELECT
      MIN(DATE_TIME)AS EF_DATE
        FROM (SELECT  C,
                      S,
                      DATE_TIME 
                        FROM (SELECT C,
                          S,
                          DATE_TIME,
                                      CASE
                                      WHEN lead over ( order by ) IS NULL
                                        THEN 'Y'
                                        ELSE 'N'
                                      END AS flag
                          FROM table_2
                              )aicuri
                WHERE flag = 'Y') Z
      WHERE CU_CODE = A.cR_CODE 
      AND Z.DATE_TIME     > A.DATE_TIME
      )aicurin 
) AS EXPI_DATE

My questions is:

1. What is the efficient way to do this since hive forbid the use of NVL(),()

All the help and ideas will be appreciated!

Answer 1

Does COALESCE work for you in this case?

SELECT COALESCE(
    (SELECT MIN(DATE_TIME) FROM (SELECT ...)),
    (SELECT MIN(DATE_TIME) FROM (SELECT ...))
) AS EXPI_DATE