Re: PL/SQL Cursor : Is this the best way?

From: Kenny Gump <kgump_at_mylanlabs.com>
Date: Wed, 16 Jun 1999 11:21:16 -0400
Message-ID: <3767c0eb.0@news.mountain.net>

If I understand your rule correctly, the cursor you have won't give you what

you want.  Your cursor will give you a listing of a.name and a.status where
the name is in both table_a and table_b and table_a.status is 'P'.  If you
are wanting to get all the table_a.name and table_a.status where

table_a.status = 'P' and there is no matching name in table_b, try the following:

CURSOR     rule_cursor IS
SELECT      a.name, a.status
FROM         table_a   a
WHERE      a.status = 'P'
AND           a.name NOT IN
                   (SELECT   b.name
                    FROM     table_b);

In this query and yours the sub-query will only execute once.

Kenny Gump

Altitude wrote in message <37677703.A2A18E82_at_logica.com>...
>Hi,
>
>I'm writing an integrity checker for our system at work which basically
>scans the database tables for any discrepant pieces of data that should
>not be there. Anyway, there's one rule which is as follows :
>
>(I've changed the names of tables and columns)
>
>For all names in table_a whose status is 'P', a record should exist in
>table_b. I want a list of all names and corresponding status which DO
>NOT meet this rule.
>I've come up with the following cursor in PL/SQL (Oracle 7.3.3) :
>
>CURSOR rule_cursor IS
>SELECT a.name, a.status
>FROM table_a a
>WHERE a.status = 'P'
>AND a.name NOT IN
> (SELECT b.name
> FROM table_a a,
> table_b b
> WHERE a.name = b.name);
>
>My question is this ... is there an easier, more efficient way of doing
>this? I'm fairly new to PL/SQL, and to me, it seems an awful amount of
>work to be done for something which seems straight forward. I've tried
>more "simpler" SELECTS, but all come back with incorrect results.
>
>Any help will be greatly appreciated.
>Thanks in advance.
>
>
Received on Wed Jun 16 1999 - 10:21:16 CDT