DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Is there a Not Exists Operator

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    Hi,

    Like exists is there a not exists operator in sql.

    i want to use it in a if condition

    for example

    if variable_name not exists (select from < table_name>) then......


    How do I do this.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sure, it works just like EXISTS...
    Jeff Hunter

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    I tried using the exists poerator in the If condition bur it gives me an error

    SQL> @c:\companydb\news\loadnews
    if i.primary_exchange not exists (select * from lkp_exchange
    *
    ERROR at line 294:
    ORA-06550: line 294, column 28:
    PLS-00103: Encountered the symbol "EXISTS" when expecting one of the following:
    in like between
    ORA-06550: line 295, column 21:
    PLS-00103: Encountered the symbol "THEN" when expecting one of the following:
    , ; and or
    ORA-06550: line 309, column 2:
    PLS-00103: Encountered the symbol "INSERT"
    ORA-06550: line 332, column 1:
    PLS-00103: Encountered the symbol "END" when expecting one of the following:
    begin function package pragma procedure form


    Can somebody give me the syntax on how to use the exists / not exists operator as

    if variable_name not exists (select from < table_name>) then

    Thanks
    Ronnie


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You're using it wrong. See http://technet.oracle.com/docs/produ...or.htm#1016309 for details
    Jeff Hunter

  5. #5
    Join Date
    Feb 2001
    Posts
    82
    your following the microsoft's way.... you can only use not exists in a single DDL/DML statement

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,
    I was wondering if we can do something like this in Oracle

    If exists (select * from table_name where ..) then
    ....

    end if;


    Thanks
    Ronnie


  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    As they already alluded... No.

    The EXISTS function is SQL-only in Oracle. While you can do this nicely in SQLServer, Oracle provides no such immediate functionality.

    The best you can do is something like:

    SELECT COUNT(*) INTO var FROM DUAL WHERE EXISTS (select 1 from org where org_pk = 423423);
    IF (var = 1) THEN
    ...


    Or you can wrap this in dynamic SQL, wrap that inside a function and make your own EXISTS function that *can* be called inside PL/SQL. That's what I did.

    HTH,

    - Chris


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width