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

In article <31u5h4$e3l_at_dcsun4.us.oracle.com> rkrishna_at_us.oracle.com (Ramesh (cntr - rfisher) Krishnamurthy) writes:
>In article <1994Aug4.142944.11336_at_KYnug.org> Neil Greene <neil_at_kynug.org> 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.
>>
>>I was thinking something that uses the group function and having count > 1
>>would do it, but that doesn't work. Any suggestions?
>>--
>>Neil Greene
>>benchMark Developments, Inc. [NeXT VAR]
>>2040 Regency Road, Suite C Lexington, KY 40503
>>Phone: 606-231-6599 / Fax: 606-254-4864
>
>Try this .
>
>In the beginning of your script
>Create view SS_tot as select SS,count(*) total
>from master
>group by SS having count(*) > 1;
>
>In the SQl statement include
>
>and SS in (select SS from SS_tot);
>
>
>At the end of the script drop the view if you do not want it for any further
>process.
>
>Hope this helps
>
>Ramesh Krishnamurthy
>
>

Since Hank Robinson has given a better solution to the SQL problem,I withdraw this. Thanks Hank.(refer article no.16979)

Ramesh Krishnamurthy Received on Fri Aug 05 1994 - 23:12:40 CEST