nvl

mpbci0fu  于 2021-06-24  发布在  Hive
关注(0)|答案(1)|浏览(450)

当它们之间有子查询时,是否可以调用nvl?代码如下。

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,

我们知道,系统限制我们在选择中使用make select:https://cwiki.apache.org/confluence/display/hive/subqueries+in+select#space-菜单链接内容
此外,我们只能在窗体如下所示时使用case:

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

我要用什么样的形式来成功呢?

mm9b1k5b

mm9b1k5b1#

假设temp.ab\ficant.key\u col是我在sql下面创建的驱动表中的键。两个子查询都转换为左联接。现在,如果您没有键列,您可以连接到某个列上,该列在函数中是唯一的。

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

相关问题