Home » SQL & PL/SQL » SQL & PL/SQL » General SQL (11 g)
General SQL [message #567802] Fri, 05 October 2012 08:45 Go to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hello,
I have the following table:

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




Re: General SQL [message #567804 is a reply to message #567802] Fri, 05 October 2012 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about using "AND" in your CASE tests?

Regards
Michel
Re: General SQL [message #567805 is a reply to message #567804] Fri, 05 October 2012 08:51 Go to previous messageGo to next message
primer2020
Messages: 32
Registered: August 2012
Member
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
Re: General SQL [message #567806 is a reply to message #567804] Fri, 05 October 2012 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: General SQL [message #567811 is a reply to message #567805] Fri, 05 October 2012 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
primer2020 wrote on Fri, 05 October 2012 15:51
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
Re: General SQL [message #567853 is a reply to message #567811] Sun, 07 October 2012 03:44 Go to previous messageGo to next message
Flyby
Messages: 143
Registered: March 2011
Location: Belgium
Senior Member
something like
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

?

[Updated on: Sun, 07 October 2012 03:45]

Report message to a moderator

Re: General SQL [message #567854 is a reply to message #567853] Sun, 07 October 2012 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it will not going to work in his case. Twisted Evil

Regards
Michel
Re: General SQL [message #567868 is a reply to message #567805] Sun, 07 October 2012 17:13 Go to previous message
matthewmorris68
Messages: 203
Registered: May 2012
Location: Orlando, FL
Senior Member

Create a PL/SQL function to perform your calculation that is too complex to be done by adding an AND to CASE. Call the function from a SQL statement and pass it x, y, and z. I can't be more specific because you haven't been.

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;
Previous Topic: People who will reach legal age after one month
Next Topic: create table statement length > 4000
Goto Forum:
  


Current Time: Wed Jul 30 02:17:41 CDT 2014

Total time taken to generate the page: 0.19419 seconds