NVL between subqueries in HIVEQL and Talend

Question

Is it possible to call nvl when there are subqueries between them? Code is below.

     NVL(
            (
            SELECT
            MIN(DATE_TIME)AS EFFECTIVE_DATE
            FROM TEMP.DM_ZEXPIDA_FICANT Z
            WHERE REGEXP_EXTRACT(CURR_HC,'([^;]+)',0)  = TEMP.AB_FICANT.CURR_CODE
            AND Z.DATE_TIME                            > TEMP.AB_FICANT.DATE_TIME
            ),
            (
            SELECT
            MIN(DATE_TIME)AS EFFECTIVE_DATE
            FROM TEMP.DM_ZEXPIDB_FICANT Z
            WHERE CURR_CODE = TEMP.AB_FICANT.CURR_CODE
            AND Z.DATE_TIME > TEMP.AB_FICANT.DATE_TIME
            ) 
    )AS EXPI_DATE,

As we know that the system restrict us for make select inside select: https://cwiki.apache.org/confluence/display/Hive/Subqueries+in+SELECT#space-menu-link-content

Also, we can only use CASE WHEN when the form is like this :

CASE WHEN rr IS NULL THEN kk
ELSE rr 
END AS EXPI_DATE

What kind of form I have to try to succeed this?

Answer 1

Assuming TEMP.AB_FICANT.KEY_COL as KEY in the driving table i created below SQL. Both subqueries are converted to left join. Now, if you do not have a key column, you can join on some column which is unique in AB_FICANT.

SELECT NVL( A.EFFECTIVE_DATE, B.EFFECTIVE_DATE) AS EXPI_DATE
    FROM TEMP.AB_FICANT DRIV
    LEFT JOIN (
            SELECT
            MIN(DATE_TIME)AS EFFECTIVE_DATE, INNER_DRIV.KEY_COL
            FROM TEMP.DM_ZEXPIDA_FICANT Z, TEMP.AB_FICANT INNER_DRIV
            WHERE REGEXP_EXTRACT(Z.CURR_HC,'([^;]+)',0)  = INNER_DRIV.CURR_CODE
            AND Z.DATE_TIME                            > INNER_DRIV.DATE_TIME
            GROUP BY INNER_DRIV.KEY_COL
            ) A ON A.KEY_COL=DRIV.KEY_COL
    LEFT JOIN (
            SELECT
            MIN(DATE_TIME)AS EFFECTIVE_DATE, INNER_DRIV.KEY_COL
            FROM TEMP.DM_ZEXPIDB_FICANT Z, TEMP.AB_FICANT INNER_DRIV
            WHERE CURR_CODE = INNER_DRIV.CURR_CODE
            AND Z.DATE_TIME > INNER_DRIV.DATE_TIME
            GROUP BY INNER_DRIV.KEY_COL
            ) B ON  B.KEY_COL=DRIV.KEY_COL