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)
时间: 2024-01-26 11:03:20 浏览: 208
这个报错与前一个类似,是因为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语句而得到正确的结果。
相关推荐


















