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

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 Received on Fri Aug 05 1994 - 21:56:20 CEST