WITH TAB_B AS ( select DISTINCT A.calling_imsi as IMSI, coalesce(B.ANTIGUA,C.ANTIGUA) GJ, coalesce(B.APUA,C.APUA) YYS from ( SELECT A.calling_imsi, substr(a.calling_imsi,1,6) calling_imsi_6, substr(a.calling_imsi,1,5) calling_imsi_5, substr(a.calling_imsi,1,4) calling_imsi_4 FROM spark_odc_dwd.D_ENS_GBSSAP_CC a WHERE p_hour='#{time yyyyMMddHH}' ) a left join ( select SUBSTR(regexp_replace(IMSI,'[^0-9]+',''),1,6) IMSI_6, SUBSTR(regexp_replace(IMSI,'[^0-9]+',''),1,5) IMSI_5, ANTIGUA, APUA from spark_odc_data.A_DM_CO_BA_VR_OPT_IMSI )b on a.calling_imsi_6=b.IMSI_6 left join ( select SUBSTR(regexp_replace(IMSI,'[^0-9]+',''),1,6) IMSI_6, SUBSTR(regexp_replace(IMSI,'[^0-9]+',''),1,5) IMSI_5, ANTIGUA, APUA from spark_odc_data.A_DM_CO_BA_VR_OPT_IMSI )c on a.calling_imsi_5=c.IMSI_5)
时间: 2024-04-26 15:25:03 浏览: 19
这是一个SQL语句,它的作用是从表spark_odc_dwd.D_ENS_GBSSAP_CC中筛选出符合条件p_hour='#{time yyyyMMddHH}'的数据,并与表spark_odc_data.A_DM_CO_BA_VR_OPT_IMSI进行左连接,最终得到一个名为TAB_B的表,它包含IMSI、GJ和YYS三个字段。其中,IMSI字段来自表spark_odc_dwd.D_ENS_GBSSAP_CC中的calling_imsi字段,GJ和YYS字段则是通过左连接得到的。其中coalesce函数用于处理null值,如果B.ANTIGUA或C.ANTIGUA为空,则选择另一个不为空的值作为GJ字段的值;同理,如果B.APUA或C.APUA为空,则选择另一个不为空的值作为YYS字段的值。
相关问题
mismatched input 'WITH' expecting {'(', 'SELECT', 'FROM', 'VALUES', 'TABLE', 'INSERT', 'MAP', 'REDUCE'}(line 1, pos 1191) == SQL == WITH TAB_A AS ( select DISTINCT A.IMSI, coalesce(B.COUNTRY,C.COUNTRY) GJ, coalesce(B.OPERATOR,C.OPERATOR) YYS from ( SELECT A.START_TIME, A.END_TIME, A.MSISDN, A.IMSI, A.CALLING_GT, A.OPERATE_CODE, A.RESULT, A.ERROR_CODE, substr(a.CALLING_GT,1,6) CALLING_GT_6, substr(a.CALLING_GT,1,5) CALLING_GT_5, substr(a.CALLING_GT,1,4) CALLING_GT_4 FROM spark_odc_dwd.D_ENS_GMAP_MM a WHERE p_hour='2023060600' ) a left join ( SELECT DISTINCT regexp_replace(E164_IR21,'[^0-9]+','') E164_IR21, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,6) E164_IR21_6, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,5) E164_IR21_5, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,4) E164_IR21_4, COUNTRY, VENDOR_NAME AS OPERATOR FROM spark_odc_data.A_DM_CO_BA_VR_OPT_GT A) b on A.CALLING_GT_5=b.E164_IR21_5 left join ( SELECT DISTINCT regexp_replace(E164_IR21,'[^0-9]+','') E164_IR21, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,6) E164_IR21_6, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,5) E164_IR21_5, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,4) E164_IR21_4, COUNTRY, VENDOR_NAME AS OPERATOR FROM spark_odc_data.A_DM_CO_BA_VR_OPT_GT A) c on A.CALLING_GT_4=c.E164_IR21_4)
这个报错与前一个类似,是因为WITH语句不被支持或者不被SQL版本所支持。你可以将WITH语句改写成子查询的形式,例如:
```
SELECT DISTINCT A.IMSI, coalesce(B.COUNTRY,C.COUNTRY) AS GJ, coalesce(B.OPERATOR,C.OPERATOR) AS YYS
FROM (
SELECT START_TIME, END_TIME, MSISDN, IMSI, CALLING_GT, OPERATE_CODE, RESULT, ERROR_CODE, substr(CALLING_GT,1,6) AS CALLING_GT_6, substr(CALLING_GT,1,5) AS CALLING_GT_5, substr(CALLING_GT,1,4) AS CALLING_GT_4
FROM spark_odc_dwd.D_ENS_GMAP_MM
WHERE p_hour='2023060600'
) A
LEFT JOIN (
SELECT DISTINCT regexp_replace(E164_IR21,'[^0-9]+','') AS E164_IR21, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,6) AS E164_IR21_6, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,5) AS E164_IR21_5, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,4) AS E164_IR21_4, COUNTRY, VENDOR_NAME AS OPERATOR
FROM spark_odc_data.A_DM_CO_BA_VR_OPT_GT
) B ON A.CALLING_GT_5=B.E164_IR21_5
LEFT JOIN (
SELECT DISTINCT regexp_replace(E164_IR21,'[^0-9]+','') AS E164_IR21, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,6) AS E164_IR21_6, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,5) AS E164_IR21_5, SUBSTR(regexp_replace(E164_IR21,'[^0-9]+',''),1,4) AS E164_IR21_4, COUNTRY, VENDOR_NAME AS OPERATOR
FROM spark_odc_data.A_DM_CO_BA_VR_OPT_GT
) C ON A.CALLING_GT_4=C.E164_IR21_4;
```
这样就可以避免使用WITH语句而得到正确的结果。
SELECT DISTINCT A.BEGIN_REPAIR_NO, A.PART_NO, A.CREATE_BY, B.EMPLOYEE_NAME AS CREATE_NAME, A.CREATE_TIME FROM TPMDATA.PICKING A LEFT JOIN TPMDATA.SAP_EMPLOYEE B ON A.CREATE_BY = B.EMPLOYEE_NO SELECT nvl(sum(A.CURRENT_QUANTITY),0) FROM TPMDATA.PICKING A LEFT JOIN TPMDATA.sys_part B ON A.PART_NO = B.PART_NO 将这俩条sql语句 合成一条
可以使用子查询将这两条 SQL 查询语句合并成一条:
SELECT DISTINCT
A.BEGIN_REPAIR_NO,
A.PART_NO,
A.CREATE_BY,
B.EMPLOYEE_NAME AS CREATE_NAME,
A.CREATE_TIME,
NVL((SELECT SUM(CURRENT_QUANTITY)
FROM TPMDATA.PICKING
LEFT JOIN TPMDATA.sys_part ON TPMDATA.PICKING.PART_NO = TPMDATA.sys_part.PART_NO
WHERE TPMDATA.PICKING.BEGIN_REPAIR_NO = A.BEGIN_REPAIR_NO AND TPMDATA.PICKING.PART_NO = A.PART_NO), 0) AS CURRENT_QUANTITY
FROM
TPMDATA.PICKING A
LEFT JOIN TPMDATA.SAP_EMPLOYEE B ON A.CREATE_BY = B.EMPLOYEE_NO;
这条语句将原本要查询的 SUM(CURRENT_QUANTITY) 作为子查询嵌入到了主查询中,并且增加了 WHERE 子句,以便查询对应的 BEGIN_REPAIR_NO 和 PART_NO。
相关推荐
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)