Re: [Q]: sql or pl/sql solution

neil_at_kynug.org (Neil Greene) writes:

> I have the following sql script:
 

> break on ss skip 1
> column fname format a20
> column lname format a20
> spool &spoolfile
> set termout off
> SELECT
> ss, nasrisid, fname, lname, birthdate
> FROM
> master
> WHERE
> ss > '0'
> ORDER BY
> ss asc;
> spool off
 

> That creates reports like the following:
 

> SS NASRISID FNAME LNAME BIRTHDATE
> --------- ---------- -------------------- -------------------- ---------
> 999999999 972843 GINNY BELDING 19-FEB-64
> 972863 BERNIE PLINIO 28-OCT-62
> 974959 DONNA KENDALL 13-MAY-70
> 974991 MICHAEL TABOR 28-OCT-44
> 974897 JR., GARCIA 05-JUN-71
> 974914 LOLETHA SAUNDERS 22-OCT-53
> 975100 GEIR SMEBY 12-AUG-68
> 975101 ROAR SMEBY 19-SEP-70
> 975104 ANJA ZUHLSDORF 26-AUG-67
> 975165 ANDREW SMITH 14-AUG-49
> 975273 MITCHELL WOOLRICH 30-SEP-37
 
> 101010101 972843 SMITH BELDING 19-FEB-64
 
> 101010102 972843 JAMES BELDING 19-FEB-64
 
> 101010103 972843 GREEN BELDING 19-FEB-64
 
> All of the applicants in the database a grouped by matching social
> security numbers. Which is exactly what I want, except, I do not want the
> report to print if there is only one applicant witha particular social
> security number, only where there is more then one. Of course, using the
> group function, I can't get the individual rows to print. Is there a way
> to do this in straight sql or do I need to write a pl/sql solution to
> this.

Straight sql is perfectly capable;

SELECT
        ss, nasrisid, fname, lname, birthdate FROM
        master a
WHERE

        ss > '0' and exists (SELECT
                                     null
                             FROM
                                     master
                             WHERE
                                     ss = a.ss and nasrisid <> a.nasrisid)
ORDER BY
        ss asc;

I'm assuming that your records, in the master table, are uniquely identified by ss and nasrisid together.

> I was thinking something that uses the group function and having count > 1
> would do it, but that doesn't work. Any suggestions?

Hope this helps.

> Neil Greene
> benchMark Developments, Inc. [NeXT VAR]
> 2040 Regency Road, Suite C Lexington, KY 40503
> Phone: 606-231-6599 / Fax: 606-254-4864

Hank Robinson.
Oracle DBA,
University of Warwick. Received on Thu Aug 04 1994 - 21:47:18 CEST