Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
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) –  ypercube Feb 7 '13 at 21:39
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
Does it also work in Select statement ? –  Alejandro Bastidas Feb 7 '13 at 21:42
    
@AlejandroBastidas no but what do you need to do in a SELECT here? There is almost certainly another way to do it. –  JNK Feb 7 '13 at 21:43
    
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. –  Alejandro Bastidas Feb 7 '13 at 21:54
    
Well you CAN use a case for that: SELECT MyField = CASE WHEN x>1 THEN ColumnA WHEN X<1 THEN ColumnB ELSE ColumnC –  JNK Feb 7 '13 at 21:59
    
I'm ready to continue translating procedures. Thank you very much. Even if it may be a simple issue, you really helped me out. –  Alejandro Bastidas Feb 7 '13 at 22:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.