How to return different statements in where clause ? (oracle)

Question

I mean:

CREATE OR REPLACE FUNCTION FUNCTION1(ID IN NUMBER) RETURN NUMBER
BEGIN
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
WHERE COLUMN1<= Y AND (CASE WHEN ID < X THEN COLUMN1<COLUMN2 ELSE COLUMN2>COLUMN3 END),
AND COLUMN 3 = XYZ;
RETURN SOMETHING.
END;

I know case statement does not return an expression but a value. How can i use some kind of "conditional statements" or something to return expressions ? I use to do it easily in mysql with "IF conditional" but i don't know how to do it in Oracle 11g.
EDIT: Does it work the same, not only for where statemen but for select ?

• ypercubeᵀᴹ: I think you just need to change the (CASE WHEN ID < X THEN COLUMN1<COLUMN2 ELSE COLUMN2>COLUMN3 END) to: (ID < X AND COLUMN1<COLUMN2 OR (ID>=X OR ID IS NUL OR X IS NULL) AND COLUMN2>COLUMN3)

Answer 1

Just use grouped WHERE conditions:

WHERE COLUMN1<= Y 
 AND ((ID < X AND COLUMN1<COLUMN2)
 OR(ID >= X AND COLUMN2>COLUMN3))
AND COLUMN 3 = XYZ;

Remember you really just want to get some boolean evaluations back here - as long as you can reduce your requirements to distinct sets of conditions you can put it into WHERE which will be plenty fast.

The time to use CASE is if you have overlapping criteria that you need to short circuit.

• Alejandro Bastidas: Does it also work in Select statement ?
• JNK: @AlejandroBastidas no but what do you need to do in a SELECT here? There is almost certainly another way to do it.
• Alejandro Bastidas: Not in this example, but if I needed to choose between two or more columns to build a Select statement depending of a variable value or column value, should it work? I'm kind of lost. Thank your very much.
• JNK: Well you CAN use a case for that: SELECT MyField = CASE WHEN x>1 THEN ColumnA WHEN X<1 THEN ColumnB ELSE ColumnC
• Alejandro Bastidas: I'm ready to continue translating procedures. Thank you very much. Even if it may be a simple issue, you really helped me out.