IF EXISTS condition not working with PLSQL
160,336
Solution 1
IF EXISTS()
is semantically incorrect. EXISTS
condition can be used only inside a SQL statement. So you might rewrite your pl/sql block as follows:
declare
l_exst number(1);
begin
select case
when exists(select ce.s_regno
from courseoffering co
join co_enrolment ce
on ce.co_id = co.co_id
where ce.s_regno=403
and ce.coe_completionstatus = 'C'
and ce.c_id = 803
and rownum = 1
)
then 1
else 0
end into l_exst
from dual;
if l_exst = 1
then
DBMS_OUTPUT.put_line('YES YOU CAN');
else
DBMS_OUTPUT.put_line('YOU CANNOT');
end if;
end;
Or you can simply use count
function do determine the number of rows returned by the query, and rownum=1
predicate - you only need to know if a record exists:
declare
l_exst number;
begin
select count(*)
into l_exst
from courseoffering co
join co_enrolment ce
on ce.co_id = co.co_id
where ce.s_regno=403
and ce.coe_completionstatus = 'C'
and ce.c_id = 803
and rownum = 1;
if l_exst = 0
then
DBMS_OUTPUT.put_line('YOU CANNOT');
else
DBMS_OUTPUT.put_line('YES YOU CAN');
end if;
end;
Solution 2
Unfortunately PL/SQL doesn't have IF EXISTS
operator like SQL Server. But you can do something like this:
begin
for x in ( select count(*) cnt
from dual
where exists (
select 1 from courseoffering co
join co_enrolment ce on ce.co_id = co.co_id
where ce.s_regno = 403
and ce.coe_completionstatus = 'C'
and co.c_id = 803 ) )
loop
if ( x.cnt = 1 )
then
dbms_output.put_line('exists');
else
dbms_output.put_line('does not exist');
end if;
end loop;
end;
/
Author by
nirmalgyanwali
Updated on July 16, 2020Comments
-
nirmalgyanwali almost 4 years
I am trying to print the TEXT when condition is TRUE. The select code is perfectly working fine. It's showing 403 value when i only run select code. But I have to print some text when condition exists. What's the problem with following code.
BEGIN IF EXISTS( SELECT CE.S_REGNO FROM COURSEOFFERING CO JOIN CO_ENROLMENT CE ON CE.CO_ID = CO.CO_ID WHERE CE.S_REGNO=403 AND CE.COE_COMPLETIONSTATUS = 'C' AND CO.C_ID = 803 ) THEN DBMS_OUTPUT.put_line('YES YOU CAN'); END;
Here is the error report:
Error report: ORA-06550: line 5, column 1: PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: ) , with group having intersect minus start union where connect 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
-
nirmalgyanwali over 11 yearsThank you Nicholas for your brilliant code. It perfectly work for me.
-
nirmalgyanwali over 11 yearsThank you Hilton for your help. Ya, I got the idea from your code. But I don't know why the condition is FALSE and it's showing ELSE condition. The value in x.cnt is 0.
-
HiltoN over 11 yearsProbably this is because I lowercased your query with constant 'C', that should be in upper case.
-
miracle_the_V almost 9 years"simply use count function" is much less efficient. Stick with exists/not exists.
-
zygimantus over 7 yearsWhy
exists
cannot be used in insert statements? -
William Robertson about 7 years
exists
certainly can be used withinsert
statements.