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

In article <31rgk6$reo_at_crocus.csv.warwick.ac.uk> cudau_at_csv.warwick.ac.uk (Hank Robinson) writes:
>
>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.

or Try this

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

        ss > '0' and 1 < (select count(*) from master b where
                          b.ss = a.ss)

Hope this helps

Ramesh Krishnamurthy Received on Mon Aug 08 1994 - 20:06:30 CEST