Home » SQL & PL/SQL » SQL & PL/SQL » can a CASE is included in Where Clause.....
can a CASE is included in Where Clause..... [message #251325] Thu, 12 July 2007 23:29 Go to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hello friends,
Can a CASE is include in WHERE CLAUSE. because i need to check the condition according to the value. Or is there any other way...




SELECT  a.name,
		b.data,
		a.basiccd,
		a.status,
		c.name,
		b.txday,
		b.remark
from	ifapl a,
		wmmr b,
		psmr c,
		psmt d
where	b.mtelint = d.init and
		case 
			WHEN b.actionkey = '1' THEN
				 a.actno = b.data
			WHEN b.actionkey = '2' THEN
				 a.aano = b.data	 
		end; 		 






thx
Ajay
Re: can a CASE is included in Where Clause..... [message #251327 is a reply to message #251325] Thu, 12 July 2007 23:48 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
This can be tested quite easy:
SQL> drop table faq;

Table dropped.

SQL> create table faq (id number, text1 varchar2(10), text2 varchar2(10));

Table created.

SQL> insert all
  2  into faq values (1, 'One', 'enO')
  3  into faq values (2, 'Two', 'owT')
  4  into faq values (3, 'Three', 'eerhT')
  5  into faq values (4, 'Four', 'ruoF')
  6  select 1 from dual;

4 rows created.

SQL> 
SQL> declare
  2    l_variable varchar2(10);
  3    l_result   number;
  4  begin
  5    l_variable := 'One';
  6    select id
  7    into   l_result
  8    from   faq
  9    where  1 = 1
 10    and    case when 1 = 2 then text2 = l_variable
 11  		   else text1 = l_variable
 12  	      end
 13    ;
 14    dbms_output.put_line('Found text '||l_result);
 15  end;
 16  /
  and    case when 1 = 2 then text2 = l_variable
                                    *
ERROR at line 10:
ORA-06550: line 10, column 37:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored


SQL> Prompt The correct way:
The correct way:
SQL> declare
  2    l_variable varchar2(10);
  3    l_result   number;
  4  begin
  5    l_variable := 'One';
  6    select id
  7    into   l_result
  8    from   faq
  9    where  1 = 1
 10    and    l_variable = case when 1 = 2 then text2
 11  				else text1
 12  			   end
 13    ;
 14    dbms_output.put_line('Found text '||l_result);
 15  end;
 16  /
Found text 1

PL/SQL procedure successfully completed.

SQL> 
SQL> spool off
Previous Topic: Database level DML triggers
Next Topic: need query help
Goto Forum:
  


Current Time: Sat Dec 14 13:41:52 CST 2024