Franky's Blog

书山有路勤为径，学海无涯苦作舟……

Only 1 SubQuery Expression Is Supported

   Franky    Hive    ,

HiveQL Where条件中只支持一个子查询，如下面的例子中我需要排除两类数据：

  1. 排除POS商户不统计名单
  2. 排除POS商户MCC码不统计名单
1INSERT INTO TABLE TEST.TMP03_POS_AMT
2SELECT *
3FROM TEST.TMP02_POS_AMT
4WHERE POS_MERCHT_CD NOT IN (SELECT POS_MERCHT_ID FROM TEST.MERCH_LIST)
5   OR MCC NOT IN (SELECT MCC FROM TEST.MCC_INFO)
6   ;

SemanticException [Error 10249]: Line 5 Unsupported SubQuery Expression ‘MCC’: Only 1 SubQuery expression is supported.

修改方式1：
 1INSERT INTO TABLE TEST.TMP03_POS_AMT
 2SELECT *
 3FROM TEST.TMP02_POS_AMT
 4WHERE POS_MERCHT_CD NOT IN (SELECT POS_MERCHT_ID FROM TEST.MERCH_LIST)
 5;
 6
 7INSERT INTO TABLE TEST.TMP04_POS_AMT
 8SELECT *
 9FROM TEST.TMP03_POS_AMT
10WHERE MCC NOT IN (SELECT MCC FROM TEST.MCC_INFO)
11;
修改方式2：
 1INSERT INTO TABLE TEST.TMP03_POS_AMT
 2SELECT *
 3FROM TEST.TMP02_POS_AMT A
 4LEFT JOIN TEST.MERCH_LIST B
 5ON A.POS_MERCHT_CD = B.POS_MERCHT_CD
 6LEFT JOIN TEST.MCC_INFO C
 7ON A.MCC = C.MCC
 8WHERE B.POS_MERCHT_CD IS NULL
 9  AND C.MCC IS NULL
10  ;

Share