Case on where clause ORACLE

3
votes
SELECT *
FROM (
                SELECT P.PC_ID, PC.PC, P.BLOK_ID, B.BLOK, B.ID_MATERIAL, M.MATERIAL, M.NO_MATERIAL, P.START_DTTM, TO_CHAR(P.START_DTTM,'yyyy-mm-dd') DATE_PERENCANAAN
                FROM UTSG_PERENCANAAN P
                INNER JOIN UTSG_PC PC
                        ON P.PC_ID = PC.ID_PC
                INNER JOIN UTSG_BLOK B
                        ON P.BLOK_ID = B.ID_BLOK
                LEFT JOIN UTSG_MATERIAL M
                        ON B.ID_MATERIAL = M.ID_MATERIAL
                WHERE P.NO_LAMBUNG = '341'
                                AND P.LOKASI_ID = '2'
                                AND P.START_DTTM < TO_DATE('2019-01-09 23:40:52', 'yyyy-mm-dd hh24:mi:ss')
                ORDER BY P.START_DTTM DESC
)
WHERE 
    CASE
        WHEN BLOK = 'DD11'
            THEN ROWNUM <= 1
        ELSE
            THEN ROWNUM <= 2
    END

I have query like this, on case in where clause always show

error ORA-00905: missing keyword

3
sqloraclecase
Could you provide some sample data and expect result? - D-Shih
@D-Shih why? We don't need sample data in order to fix a syntax error. - ADyson
It's generally better to use AND/OR constructions instead of case expressions in the WHERE clause. - jarlh
While the given answers are syntactical correct, I assume they still don't produce the expected result. But as you didn't explain what you expect the query to return, it's hard to tell the correct statement. - D. Mika

3 Answers

5
votes

You can't have the comparison operator within the case statement. Instead, your where clause should be something like:

WHERE 
    rownum <= CASE WHEN BLOK = 'DD11' THEN 1
                   ELSE 2
              END
4
votes

Why use a case expression?

WHERE (
        (BLOK = 'DD11' AND ROWNUM <= 1)
        OR 
        ROWNUM <= 2
    )

In general it is advised to use "boolean logic" in where clauses, here is an blog of the topic: SQL WHERE clauses: Avoid CASE, use Boolean logic

2
votes

I think you need to replace where clause to 

((BLOK = 'DD11' and ROWNUM <= 1) or (ROWNUM <= 2))