CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
John Bustos
John Bustos

Reputation: 19544

Oracle Case in Where Clause Error

I'm working on a query that will eventually become paramaterized, but I'm getting confused about how to make my WHERE clause work.

As a simple example, suppose I have a table with a date column and I either want to get data for a date range if the boolean value is set OR just get the data for the last date in the table if the boolean = false... I figured somethign like this would work:

SELECT 
    * 
FROM 
    MyTable
WHERE
    CASE WHEN ('TRUE' = 'TRUE') THEN
        MyDate BETWEEN To_Date('2014-08-04', 'yyyy-MM-dd') AND To_Date('2014-08-05', 'yyyy-MM-dd')
    ELSE
        MyDate = (SELECT MAX(MyDate) FROM MyTable)
    END

Obviously, once I get this working, I'll put in the parameters for the boolean and the 2 dates.

But I'm getting an error for the THEN ... part of the Case clause - The BETWEEN keyword is highlighted and the error is [1]: ORA-00905: missing keyword

If I try and run it with either statement separately (MyDate BETWEEN ... or MyDate = ...), it works just fine, it's only when I put it in the CASE statement that the error occurs... What am I doing wrong?

Upvotes: 0

Views: 223

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156978

The myDate = should be pulled outside the case. However, since you use both between and = I would recommend this:

SELECT * 
FROM MyTable
WHERE ('true' = 'true' and MyDate BETWEEN To_Date('2014-08-04', 'yyyy-MM-dd') AND To_Date('2014-08-05', 'yyyy-MM-dd')
OR    ('true' != 'true' and MyDate = (SELECT MAX(MyDate) FROM MyTable))

Upvotes: 2

Related Questions