0

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 ?

  • 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
5

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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