Oracle SQL: Case Statement in where clause returns an error "single-row subquery returns more than one row"

Question

Here is a select query's where clause with a CASE condition statement. I have written this for satisfying the following requirement: "In case of any weekday, the result would contain the date of the next day and in case of Friday the result would include the dates of Saturday, Sunday and Monday"

where DTTM IN (case 
                      when to_char(sysdate,'day') <> 'friday   ' then to_date(sysdate+1,'DD/MON/YY')
                      else (select to_date(sysdate+1,'DD/MM/YYYY') + level - 1 ddate
                            from dual
                            connect by level <= to_date(sysdate+4,'DD/MM/YYYY') - to_date(sysdate+1,'DD/MM/YYYY')
                            )
                end)

I have used IN too, but I am getting an error

ORA-01427: single-row subquery returns more than one row

00000 - "single-row subquery returns more than one row"

Please help me resolve this error.

Here is the complete query for your reference:

      select * from orders 
      where DTTM IN (case 
                                when to_char(sysdate,'day') <> 'friday   ' then to_date(sysdate+1,'DD/MON/YY')
                                else (select to_date(sysdate+1,'DD/MM/YYYY') + level - 1 ddate
                                      from dual
                                      connect by level <= to_date(sysdate+4,'DD/MM/YYYY') - to_date(sysdate+1,'DD/MM/YYYY')
                                      )
                                end) 
      order by  DTTM;

A CASE statement can only return a scalar (a single value). The solution is to change the subquery to return a single row. Can you include the full query so that we see the entire logic? — Tim Biegeleisen
@TimBiegeleisen Thanks. But how to get a list of dates in return? Or is there any other way to meet the requirement, without using CASE? — Manoj Kumar
That's a case expression. (A case expression returns a value. A case statement (e.g. in stored procedures) is just conditional execution of code.) — jarlh

phonetic_man phonetic_man · Accepted Answer · 2016-06-23T06:55:04

Try the following solution.

SELECT * FROM orders 
WHERE dttm = to_date(SYSDATE+1,'DD/MON/YY')
AND TO_CHAR(SYSDATE,'day') <> 'friday'
UNION ALL
SELECT * FROM orders 
where dttm IN (SELECT TO_DATE(SYSDATE+1,'DD/MM/YYYY') + level - 1 ddate
               FROM dual
               CONNECT BY LEVEL <= to_date(sysdate+4,'DD/MM/YYYY') - to_date(sysdate+1,'DD/MM/YYYY')
               )
AND to_char(sysdate,'day') = 'friday'

The first query will run in case it is not a Friday. If it is Friday then it will not return any results. The query after UNION ALL will run only on a Friday. Only of the queries will return a result.

Oracle SQL: Case Statement in where clause returns an error "single-row subquery returns more than one row"

1 Answers