CODEWITHSUNDEEP
sqloracle-database
user2469253
user2469253

Reputation: 89

Dates verification to check of the given dates are within the range

I am trying to write a query for checking 2 dates or more simultaneously to check if one of them is out of given range. 

SELECT 
    sysdate 
FROM dual 
GROUP BY sysdate 
HAVING COUNT( CASE WHEN (SELECT sysdate, (sysdate+1) FROM dual) BETWEEN 
SYSDATE - 10 AND SYSDATE+10 THEN 1 ELSE NULL END ) > 0

I'm getting an exception

ORA-00913: too many values

when tried with 2 dates. Please advise

Upvotes: 0

Views: 35

Answers (2)

Utsav
Utsav

Reputation: 8093

The problem is what vkp suggested that case when () expects one value to compare with another, but sysdate,sysdate + 1 returns 2 values which can't be compared together. So it is better to separate both conditions, but I would have done it in where clause like below

        SELECT 
            sysdate 
        FROM dual 
        GROUP BY sysdate 
        HAVING ( select count(*) from dual 
        WHERE 
            (SELECT sysdate FROM dual) BETWEEN 
            SYSDATE - 10 AND SYSDATE+10 
        AND
            (SELECT sysdate+1 FROM dual) BETWEEN 
            SYSDATE - 10 AND SYSDATE+10 ) > 0

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Not exactly sure why you would like such a query. But as the error says, when accepts one condition and so you would have to separate the query using and. Also else null part is not needed as it is the default.

SELECT sysdate FROM dual 
GROUP BY sysdate 
HAVING COUNT(
           CASE WHEN (select sysdate from dual) BETWEEN SYSDATE - 10 AND SYSDATE+10 
           AND (select sysdate+1 from dual) BETWEEN SYSDATE - 10 AND SYSDATE+10 
           THEN 1 END
             ) > 0

Upvotes: 1

Related Questions