Home » SQL & PL/SQL » SQL & PL/SQL » CASE conditional statement
CASE conditional statement [message #243091] Wed, 06 June 2007 00:55 Go to next message
kc1982
Messages: 13
Registered: October 2006
Junior Member
FOR i IN 1..4 LOOP
SELECT COUNT(*)
INTO v_student
FROM student_detail
WHERE grade IN (CASE WHEN i = 1 THEN 'F1'
                    WHEN i = 2 THEN 'F2'  
                    WHEN i = 3 THEN 'F3' 
                    WHEN i = 4 THEN 'F4','F5'
                END);
dbms_output.put_line(Loop: '||i);
dbms_output.put_line('No of students is: '||v_student);
END LOOP;

There is no record returned when the loop goes to 4.

I want something like this
  IF i = 4 THEN
    SELECT COUNT(*)
    FROM student_detail
    WHERE grade IN ('F4','F5'); 
  END IF; 
 


I have no idea to solve the problem. I tried with
FOR i IN 1..4 LOOP
SELECT COUNT(*)
INTO v_student
FROM student_detail
WHERE grade IN (CASE WHEN i = 1 THEN 'F1'
                    WHEN i = 2 THEN 'F2'  
                    WHEN i = 3 THEN 'F3' 
                    WHEN i = 4 THEN '''F4'',''F5'''
                END);
dbms_output.put_line(Loop: '||i);
dbms_output.put_line('No of students is: '||v_student);
END LOOP;

but it does not work. Please help...thanks
Re: CASE conditional statement [message #243093 is a reply to message #243091] Wed, 06 June 2007 00:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but it does not work.
My car does not work.
Please tell me how to make it work.
You first with a solution.
Re: CASE conditional statement [message #243094 is a reply to message #243091] Wed, 06 June 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do like this. You have to:
- either put a case for each possible values in the list
- or previously fill a pl/sql table that you select in the "IN" part
- or put a statement in the IN that generate the values.

Regards
Michel
Re: CASE conditional statement [message #243095 is a reply to message #243093] Wed, 06 June 2007 01:13 Go to previous messageGo to next message
kc1982
Messages: 13
Registered: October 2006
Junior Member
anacedent wrote on Wed, 06 June 2007 00:58
>but it does not work.
My car does not work.
Please tell me how to make it work.
You first with a solution.


I dont get your meaning.
Re: CASE conditional statement [message #243097 is a reply to message #243095] Wed, 06 June 2007 01:22 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Have a look at Oracle Magazine of March - April 2007. It is available online (http://www.oracle.com/oramag). Tom Kyte talks about the "varying IN list".

MHE
Re: CASE conditional statement [message #243100 is a reply to message #243097] Wed, 06 June 2007 01:38 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or search for "varying IN list" on AskTom.

Regards
Michel
Previous Topic: Help!
Next Topic: To solve..
Goto Forum:
  


Current Time: Thu Apr 25 17:04:36 CDT 2024