IF NOT EXISTS SELECT ...
Armin S. Seidel LK/NKG
I would like to test the existence of rows as condition
in a PL/SQL if-clause:
IF NOT (EXISTS SELECT * FROM table WHERE col = value) THEN
...
END IF;
but Oracle complains about the syntax. I tried different variations
with/without parentheses without success.
Apparently, the EXISTS operator may only be used in subqueries.
Can you help?
-- Armin
_______________________________________________________________________
Armin....@hfera.ericsson.se KI/ERA/LK/NK +46(0)8-404 2456
extr.q...@memo.ericsson.se http://www.hfera.ericsson.se:/~eraarm/
ar...@independent.se www.independent.se +46(0)70-7277822
Thomas J. Kyte
On 15 Oct 1996 11:32:11 GMT, era...@hfera.ericsson.se (Armin S. Seidel LK/NKG)
wrote:
>I would like to test the existence of rows as condition
>in a PL/SQL if-clause:
> IF NOT (EXISTS SELECT * FROM table WHERE col = value) THEN
> ...
> END IF;
>
use:
select count(*) into N from dual
where exists ( select NULL from table where col = value );
if ( n = 0 ) then
....
end if;
>but Oracle complains about the syntax. I tried different variations
>with/without parentheses without success.
>Apparently, the EXISTS operator may only be used in subqueries.
>Can you help?
>
>-- Armin
>_______________________________________________________________________
>Armin....@hfera.ericsson.se KI/ERA/LK/NK +46(0)8-404 2456
> extr.q...@memo.ericsson.se http://www.hfera.ericsson.se:/~eraarm/
> ar...@independent.se www.independent.se +46(0)70-7277822
>
Thomas Kyte
Oracle Government
tk...@us.oracle.com
http://govt.us.oracle.com
---- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
-------------------
statements and opinions are mine and do not necessarily
reflect the opinions of Oracle Corporation
Steve Dodsworth
In <53vsnr$7...@newstoo.ericsson.se>, era...@hfera.ericsson.se (Armin S. Seidel LK/NKG) writes:
>I would like to test the existence of rows as condition
>in a PL/SQL if-clause:
> IF NOT (EXISTS SELECT * FROM table WHERE col = value) THEN
> ...
> END IF;
>
>with/without parentheses without success.
>Apparently, the EXISTS operator may only be used in subqueries.
>Can you help?
>
>-- Armin
>_______________________________________________________________________
>Armin....@hfera.ericsson.se KI/ERA/LK/NK +46(0)8-404 2456
> extr.q...@memo.ericsson.se http://www.hfera.ericsson.se:/~eraarm/
> ar...@independent.se www.independent.se +46(0)70-7277822
>
If you declare and fetch from a cursor, you can then use
if cursor-name%notfound then
.....
end if
Bye,
Steve
____________________________________________
| any similarity 'tween my opinions and that |
| of my employers are purely hypothetical |
| and should give no cause for alarm |
--------------------------------------------
Stephen W. Cook
Try it like this :
IF NOT EXISTS ( SELECT * FROM table WHERE col = value) THEN
good luck!
Stephen Cook
In article <53vsnr$7...@newstoo.ericsson.se>, era...@hfera.ericsson.se wrote:
>I would like to test the existence of rows as condition
>in a PL/SQL if-clause:
> IF NOT (EXISTS SELECT * FROM table WHERE col = value) THEN
> ...
> END IF;
>
>but Oracle complains about the syntax. I tried different variations
>with/without parentheses without success.
>Apparently, the EXISTS operator may only be used in subqueries.
>Can you help?
>
>-- Armin
>_______________________________________________________________________
>Armin....@hfera.ericsson.se KI/ERA/LK/NK +46(0)8-404 2456
> extr.q...@memo.ericsson.se http://www.hfera.ericsson.se:/~eraarm/
> ar...@independent.se www.independent.se +46(0)70-7277822
>
#****************************************************************************#
+DISCLAIMER: Unless indicated otherwise, everything in this note is personal +
+opinion, and not an official statement of Molecular Simulations Inc. +
#****************************************************************************#
g wayne nichols
>Try it like this :
> IF NOT EXISTS ( SELECT * FROM table WHERE col = value) THEN
>good luck!
>Stephen Cook
This produces the following PL/SQL compile error:
PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL
statement only
So you'll have to use a different approach
(which others have already suggested).
>In article <53vsnr$7...@newstoo.ericsson.se>, era...@hfera.ericsson.se wrote:
>>I would like to test the existence of rows as condition
>>in a PL/SQL if-clause:
>> IF NOT (EXISTS SELECT * FROM table WHERE col = value) THEN
>> ...
>> END IF;
>>
>>but Oracle complains about the syntax. I tried different variations
>>with/without parentheses without success.
>>Apparently, the EXISTS operator may only be used in subqueries.
>>Can you help?
>>
--
------------------------------------------------------------------------------
Computer General Rochester, NY (716) 436-6372
"... providing general computer solutions to specific business problems"
g...@servtech.com
pcol...@lehman.com
Try using Sybase - no problems except your open brachet should be after the
exists!
In Article<53vsnr$7...@newstoo.ericsson.se>, <era...@hfera.ericsson.se> writes:
> Path:
lonweb.lehman.com!news.lehman.com!uunet!in3.uu.net!news.mathworks.com!EU.net!No
rway.EU.net!sn.no!newsfeed.tip.net!news.seinf.abb.se!erinews.ericsson.se!news
> From: era...@hfera.ericsson.se (Armin S. Seidel LK/NKG)
> I would like to test the existence of rows as condition
> in a PL/SQL if-clause:
> IF NOT (EXISTS SELECT * FROM table WHERE col = value) THEN
> ...
> END IF;
>
> but Oracle complains about the syntax. I tried different variations
> with/without parentheses without success.
> Apparently, the EXISTS operator may only be used in subqueries.
> Can you help?
==================================================================
Peter Collard Please note that I am a contractor
Senior DBA so these are my own views and
Lehman Bros may not coincide with those of
London Lehman Bros.
==================================================================