SQL如下：

WITH A AS (

SELECT

   (CASE WHEN TA.SHOPID LIKE '6%' THEN '6001' ELSE TA.SHOPID END) AS SHOPID,

    --门店ID

   SUBSTR((CASE WHEN TA.SHOPID LIKE '6%' THEN '社区店' ELSE TC.SHOPNAME END),1,2) 

   AS SHOPNAME,

   --发生金额

   TB.BMID,   --部门ID

   TB.BMNAME,   --部门名称

   ROUND(SUM(COSTVALUE)/10000,4) AS COSTVALUE   --发生金额

FROM ADS_WASTEBOOK_T TA

LEFT JOIN PROCATEGORY TB

ON TA.CATEGORYID = TB.XL

LEFT JOIN SHOP TC

ON TA.SHOPID = TC.SHOPID

WHERE 

    TC.HEADSHOPID  IN ('0311','03111')

    AND TA.SHOPID NOT IN ('0001','0002')

    AND TA.SHOPID IN (SELECT T.ORG_SHOP_ID FROM DIM_ORG T WHERE T.USERID = '${fine_username}')

GROUP BY 

   (CASE WHEN TA.SHOPID LIKE '6%' THEN '6001' ELSE TA.SHOPID END) ,

    --门店ID

   SUBSTR((CASE WHEN TA.SHOPID LIKE '6%' THEN '社区店' ELSE TC.SHOPNAME END),1,2) ,

TB.BMID,

TB.BMNAME

 ),

 B AS (

SELECT

   (CASE WHEN TA.SHOPID LIKE '6%' THEN '6001' ELSE TA.SHOPID END) AS SHOPID,

    --门店ID

   SUBSTR((CASE WHEN TA.SHOPID LIKE '6%' THEN '社区店' ELSE TC.SHOPNAME END),1,2) 

   AS SHOPNAME,

TB.BMID,

TB.BMNAME,

   ROUND(SUM(COSTVALUE)/10000,4) AS TQCOSTVALUE

FROM ADS_WASTEBOOK_TQ TA

LEFT JOIN PROCATEGORY TB

ON TA.CATEGORYID = TB.XL

LEFT JOIN SHOP TC

ON TA.SHOPID = TC.SHOPID

WHERE 

    TC.HEADSHOPID  IN ('0311','03111')

    AND TA.SHOPID NOT IN ('0001','0002')

    AND TA.SHOPID IN (SELECT T.ORG_SHOP_ID FROM DIM_ORG T WHERE T.USERID = '${fine_username}')

GROUP BY 

   (CASE WHEN TA.SHOPID LIKE '6%' THEN '6001' ELSE TA.SHOPID END) ,

    --门店ID

   SUBSTR((CASE WHEN TA.SHOPID LIKE '6%' THEN '社区店' ELSE TC.SHOPNAME END),1,2) ,

TB.BMID,

TB.BMNAME

 ),

C AS (

SELECT

   (CASE WHEN TA.SHOPID LIKE '6%' THEN '6001' ELSE TA.SHOPID END) AS SHOPID,

    --门店ID

   SUBSTR((CASE WHEN TA.SHOPID LIKE '6%' THEN '社区店' ELSE TC.SHOPNAME END),1,2) 

   AS SHOPNAME,

TB.BMID,

TB.BMNAME,

   ROUND(SUM(COSTVALUE)/10000,4) AS T7COSTVALUE

FROM ADS_WASTEBOOK_T7 TA

LEFT JOIN PROCATEGORY TB

ON TA.CATEGORYID = TB.XL

LEFT JOIN SHOP TC

ON TA.SHOPID = TC.SHOPID

WHERE 

    TC.HEADSHOPID  IN ('0311','03111')

    AND TA.SHOPID NOT IN ('0001','0002')

    AND TA.SHOPID IN (SELECT T.ORG_SHOP_ID FROM DIM_ORG T WHERE T.USERID = '${fine_username}')

GROUP BY 

   (CASE WHEN TA.SHOPID LIKE '6%' THEN '6001' ELSE TA.SHOPID END) ,

    --门店ID

   SUBSTR((CASE WHEN TA.SHOPID LIKE '6%' THEN '社区店' ELSE TC.SHOPNAME END),1,2) ,

TB.BMID,

TB.BMNAME

)

SELECT 

      A.SHOPID AS SHOPID,

A.SHOPNAME AS SHOPNAME, 

A.BMID,

A.BMNAME,

A.COSTVALUE,

B.TQCOSTVALUE,

C.T7COSTVALUE

FROM A 

LEFT JOIN B ON A.BMID = B.BMID AND A.SHOPID = B.SHOPID

LEFT JOIN C ON A.BMID = C.BMID AND A.SHOPID = C.SHOPID

WHERE 

    1=1

    ${if(len(SHOPNAME) == 0,"","and SHOPNAME in ('" + SHOPNAME + "')")}

ORDER BY A.SHOPID

输入如下参数后：

报错

如何修改