关于oracle的case when then end在子查询报错的问题

关于oracle的case when then end在子查询报错的问题

SELECT t1.billdate, t3.name, SUM(t2.tot_amt_actual) AS tot_amt_actual,
       SUM(t2.qty) AS qty, to_char(SYSDATE, 'YYYYMMDD') AS today
  FROM m_retail t1, m_retailitem t2, c_customer t3
 WHERE t2.m_retail_id = t1.id
   AND t1.c_customer_id = t3.id
   AND t1.status = 2
   AND t3.name IN (CASE
         WHEN (SELECT COUNT(1)
                 FROM c_customer a
                WHERE a.name = '1') <> 0 THEN
          (SELECT a.name
             FROM c_customer a
            WHERE a.name = '浙江')
         ELSE
          (SELECT a.name
             FROM c_customer a)
       END)
 GROUP BY t1.billdate, t3.name

这个mysql可以直接写，但是在oracle里面写就会报单个子查询返回多行。
我的目的是else后返回所有的行，所以不能加rownum返还单行，要怎么修改语句。使用过wm_concat合并行，合并后查询不出结果的。
曾经也改写成以下格式，还是不行。不晓得问题出在哪里。

SELECT to_char('''' || REPLACE(wm_concat(a.name), ',', ''', ''') || '''')
FROM c_customer a

用like呢

SELECT
    t1.billdate,
    t3.NAME,
    SUM( t2.tot_amt_actual ) AS tot_amt_actual,
    SUM( t2.qty ) AS qty,
    to_char ( SYSDATE, 'YYYYMMDD' ) AS today 
FROM
    m_retail t1,
    m_retailitem t2,
    c_customer t3 
WHERE
    t2.m_retail_id = t1.id 
    AND t1.c_customer_id = t3.id 
    AND t1.STATUS = 2 
    AND t3.NAME IN (
    SELECT
        NAME 
    FROM
        c_customer
    WHERE
        NAME LIKE (
        CASE
                WHEN ( SELECT COUNT( 1 ) FROM c_customer a WHERE a.NAME = '1' ) <> 0 THEN
                ( '浙江' ) ELSE ( '%%' ) 
            END 
            ) 
        ) 
    GROUP BY
    t1.billdate,
    t3.NAME

wm_concat 用法：oracle wm_concat函数将多行转字段函数 - yclizq - 博客园

如果返回多行数据一样的话直接distinct 一下 或者 where rownum=1 只返回一行就好了

SELECT t1.billdate, t3.name, SUM(t2.tot_amt_actual) AS tot_amt_actual,
       SUM(t2.qty) AS qty, to_char(SYSDATE, 'YYYYMMDD') AS today
  FROM m_retail t1, m_retailitem t2, c_customer t3
 WHERE t2.m_retail_id = t1.id
   AND t1.c_customer_id = t3.id
   AND t1.status = 2
   AND t3.name IN (CASE
         WHEN (SELECT COUNT(1)
                 FROM c_customer a
                WHERE a.name = '1') <> 0 THEN
          (SELECT a.name
             FROM c_customer a
            WHERE a.name = '浙江'  where  rownum=1)
         ELSE
          (SELECT a.name
             FROM c_customer a where  rownum=1)
       END)
 GROUP BY t1.billdate, t3.name;

 SELECT t1.billdate, t3.name, SUM(t2.tot_amt_actual) AS tot_amt_actual,
       SUM(t2.qty) AS qty, to_char(SYSDATE, 'YYYYMMDD') AS today
  FROM m_retail t1, m_retailitem t2, c_customer t3
 WHERE t2.m_retail_id = t1.id
   AND t1.c_customer_id = t3.id
   AND t1.status = 2
   AND t3.name IN (CASE
         WHEN (SELECT COUNT(1)
                 FROM c_customer a
                WHERE a.name = '1') <> 0 THEN
          (SELECT a.name
             FROM c_customer a
            WHERE a.name = '浙江' group by a.name)
         ELSE
          (SELECT wm_concat(a.name) name
             FROM c_customer a group by a.name)
       END)
 GROUP BY t1.billdate, t3.name

改成exists 条件加进去
SELECT t1.billdate, t3.name, SUM(t2.tot_amt_actual) AS tot_amt_actual,
SUM(t2.qty) AS qty, to_char(SYSDATE, 'YYYYMMDD') AS today
FROM m_retail t1, m_retailitem t2, c_customer t3
WHERE t2.m_retail_id = t1.id
AND t1.c_customer_id = t3.id
AND t1.status = 2
AND exists (CASE
WHEN (SELECT COUNT(1)
FROM c_customer a
WHERE a.name = '1') <> 0 THEN
(SELECT a.name
FROM c_customer a
WHERE a.name = '浙江' and t3.name = a.name)
ELSE
(SELECT a.name
FROM c_customer a where t3.name = a.name)
END)
GROUP BY t1.billdate, t3.name

1、这样试下看能不能达到想要的结果（不考虑效率问题）

SELECT t1.billdate, t3.name, SUM( t2.tot_amt_actual ) AS tot_amt_actual
      , SUM( t2.qty ) AS qty, to_char( SYSDATE, 'YYYYMMDD' ) AS today
  FROM m_retail t1, m_retailitem t2, c_customer t3
 WHERE t2.m_retail_id = t1.id AND t1.c_customer_id = t3.id AND t1.status = 2
    AND t3.name IN ( SELECT a.name FROM c_customer a WHERE NOT EXISTS( SELECT 1 FROM c_customer WHERE name = '1' ) OR a.name = '浙江' )
 GROUP BY t1.billdate, t3.name