Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: select statement in plsql - if not exist ??

    hi fellow sqlers !

    under oracle and plsql i want to do this:

    leader table
    --------------
    gname: lname:
    grouppname1 leadernameX
    grouppname2 leadernameY


    groupp table
    -------------
    gname:
    grouppname1
    grouppname2
    grouppname3
    grouppname4
    grouppname5

    now i want to select all groupnames which don't have a leadername
    (= don't exist in the leader table)

    i want to use a cursor and print out the names for an option value in html

    cursor looks like this:

    CURSOR Cl IS
    SELECT grouppname
    FROM groupp
    ORDER BY grouppname;


    and then this:
    htp.p('Group <SELECT NAME=grouppnameX>');
    FOR Rl IN Cl LOOP

    htp.p('<OPTION VALUE=' || Rl.grouppname || '>' || Rl.grouppname || ' - ' || somevariableorselectstatement);
    END LOOP;


    i know in standard sql there is the not exist thing but how can i use it in pl sql and procedure ??

    thx for ideas and comments

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: select statement in plsql - if not exist ??

    You can still use NOT EXISTS in a cursor:

    CURSOR Cl IS
    SELECT grouppname
    FROM groupp g
    WHERE NOT EXISTS (SELECT 1 FROM leader l WHERE l.gname = g.gname)
    ORDER BY grouppname;

    I would prefer to do:

    CURSOR Cl IS
    SELECT grouppname
    FROM groupp
    MINUS
    SELECT grouppname
    FROM leader;

  3. #3
    Join Date
    Jun 2003
    Posts
    5
    thank you for this quick reply ..

    the MINUS works just excellent ...

Posting Permissions

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