can a CASE is included in Where Clause..... [message #251325] |
Thu, 12 July 2007 23:29 |
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 |
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
|
|
|