HIVE当前子查询表达式仅允许用作Where子句谓词

在进行一次Oracle存过迁移hive的语法中报错了

ORACLE脚本
下面展示一些 内联代码片。

// ORACLE语法
SELECT 
         (CASE  WHEN EXISTS (SELECT 1
                 FROM CHANNEL.ORGANIZATION@TO_AGENT_CHANNEL N
                WHERE N.PARTY_ID = A.ORG_ID
                  AND N.ORG_LEVEL = 7
                  AND N.ORG_TYPE = '001') THEN
          (SELECT B.COL13
             FROM CHANNEL.WORK_SHOP@TO_AGENT_CHANNEL W
                JOIN CHANNEL.CHANNEL@TO_AGENT_CHANNEL M  ON M.WORK_SHOP_ID = W.WORK_SHOP_ID
                LEFT JOIN CHANNEL.WORK_SHOP_EXT@TO_AGENT_CHANNEL B ON W.WORK_SHOP_ID =
                                                          B.WORK_SHOP_ID
            WHERE M.ORG_ID = A.ORG_ID
              )
         WHEN EXISTS (SELECT 1
                 FROM CHANNEL.ORGANIZATION@TO_AGENT_CHANNEL N
                WHERE N.PARTY_ID = A.ORG_ID
                  AND N.ORG_LEVEL = 8
                  AND N.ORG_TYPE = '001') THEN
          (SELECT B.COL13 FROM CHANNEL.WORK_SHOP@TO_AGENT_CHANNEL W
             LEFT JOIN CHANNEL.WORK_SHOP_EXT@TO_AGENT_CHANNEL B ON W.WORK_SHOP_ID =
                                                          B.WORK_SHOP_ID          
             WHERE W.ORG_ID = A.ORG_ID)
         ELSE
          NULL
       END) 归属门店编码
        FROM CHANNEL.STAFF@TO_AGENT_CHANNEL A;

// 转成hive语法
  (CASE  WHEN EXISTS (SELECT 1
                 FROM  IP_BSS_DAY.INF_CHANNEL_ORGANIZATION_D  N 
                WHERE set_day = '${yesterday}' and  N.PARTY_ID = A.ORG_ID   AND N.ORG_LEVEL = 7  AND N.ORG_TYPE = '001') THEN  c.COL13
        WHEN EXISTS (SELECT 1
                 FROM  IP_BSS_DAY.INF_CHANNEL_ORGANIZATION_D  N 
                WHERE set_day = '${yesterday}' and  N.PARTY_ID = A.ORG_ID   AND N.ORG_LEVEL = 8  AND N.ORG_TYPE = '001') THEN  d.COL13 end)  CHANNEL_NBR
   
from (select * from IP_BSS_DAY.STAFF_CHANNEL where set_day = '${yesterday}') A
left join (select * FROM IP_BSS_DAY.INF_CHANNEL_ORGANIZATION_D where set_day = '${yesterday}') B ON A.ORG_ID = B.PARTY_ID
left join  TEMP_DEVELOPER.TEMP_MI_MID_INDEX_CH_STAFF_D_1_${yesterday} c  on a.ORG_ID = c.ORG_ID
left join TEMP_DEVELOPER.TEMP_MI_MID_INDEX_CH_STAFF_D_2_${yesterday} d on a.ORG_ID = d.ORG_ID;

报错信息

语句异常:Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 1:887 Unsupported SubQuery Expression ‘‘001’’: Currently SubQuery expressions are only allowed as Where Clause predicates

查找原因

行1:887不支持的子查询表达式“001”：当前子查询表达式仅允许用作Where子句谓词
指的是hive 并不支持像oracle那样在字段里进行子查询

修改HIve sql

1、尝试将字段子查询 放在主表后面关联主表
下面展示一些 内联代码片。

// A code block
select 
   CASE  WHEN  n.PARTY_ID IS NOT NULL  THEN  c.COL13
         WHEN  M.PARTY_ID IS NOT NULL  THEN  d.COL13 end  CHANNEL_NBR
   
from (select * from IP_BSS_DAY.STAFF_CHANNEL where set_day = '${yesterday}') A
left join (select * FROM IP_BSS_DAY.INF_CHANNEL_ORGANIZATION_D where set_day = '${yesterday}') B ON A.ORG_ID = B.PARTY_ID
left join (SELECT * FROM  IP_BSS_DAY.INF_CHANNEL_ORGANIZATION_D   WHERE set_day = '${yesterday}' and   ORG_LEVEL = 7  AND ORG_TYPE = '001') N ON N.PARTY_ID = A.ORG_ID 
left join (SELECT * FROM  IP_BSS_DAY.INF_CHANNEL_ORGANIZATION_D   WHERE set_day = '${yesterday}' and   ORG_LEVEL = 8  AND ORG_TYPE = '001') M ON M.PARTY_ID = A.ORG_ID  
left join  TEMP_DEVELOPER.TEMP_MI_MID_INDEX_CH_STAFF_D_1_${yesterday} c  on a.ORG_ID = c.ORG_ID
left join TEMP_DEVELOPER.TEMP_MI_MID_INDEX_CH_STAFF_D_2_${yesterday} d on a.ORG_ID = d.ORG_ID;

结果：执行成功