create table test (x number, y number, z number);

insert into x values(50,150,450);

insert into x values(150,250,550);

insert into x values(250,350,650);

insert into x values(350,450,750);

I would like to have a case statement based on x:

select

case when x <= 100 then I WANT TO CHECK IF Y <100

when x > 100 and x <= 200 then I WANT TO CHECK IF Z <500

when x > 200 then I WANT TO CHECK IF Y+Z/100 > 200

I want to have 1 if the condition is true and 0 if false.

I tried the decode statement but no luck.

Any suggestions ?

Thank you

]]>

Regards

Michel

]]>

Is there any other way ?

Thank you]]>

http://www.orafaq.com/forum/t/88153/0/

]]>

Hello,

This is a very simple version from what I'm trying to do. I thought about the AND however it is not going to work in my case.

Is there any other way ?

Thank you

And you REALLY expect us to help to write something we have no idea?

Good luck!

Regards

Michel

]]>

select case when x <= 100 then CASE WHEN Y <100 then 1? else 0 end when x > 100 and x <= 200 then case WHEN Z <500 then 1 else 0 end when x > 200 then CASE WHEN Y+Z/100 > 200 then 1 else 0 end else 0 end myresult

?]]>

Regards

Michel

]]>

CREATE FUNCTION mycase (p_x NUMBER, p_y NUMBER, p_z NUMBER) RETURN NUMBER AS v_retval NUMBER; BEGIN CASE p_x WHEN <= 100 THEN -- Perform whatever check you want however complex here; WHEN > 100 AND <= 200 THEN -- Perform whatever check you want however complex here; WHEN > 200 THEN -- Perform whatever check you want however complex here; ELSE v_retval := 0; END CASE; RETURN v_retval; END mycase;