| SQL Coding Question [message #20383] |
Mon, 20 May 2002 12:33  |
David Frankel
Messages: 2
Registered: March 2002
|
Junior Member |
|
|
I need help with some SQL I am trying to write. Let's say I have two fields in a table:
Reference_ID and Trading_Partner...
How can I tell if one reference ID is associated with MORE THAN ONE trading partner?
In other words, I would NOT need to flag this:
Reference ID Trading Partner
99999999999 Mellon Bank
But this I WOULD WANT to flag this using SQL:
Reference ID Trading Partner
99999999999 Mellon Bank
99999999999 PNC Bank
Any help would be appreciated. Thanks, David
frankel.da@mellon.com
|
|
|
|
| Re: SQL Coding Question [message #20385 is a reply to message #20383] |
Mon, 20 May 2002 14:09  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
You can identify, the rows.
But how do want to flag them? any pl/sql is allowed?
SQL> select * from test5;
REFID PARTNER
---------- --------------------
99999 Mellon Bank
99999 Mellon Bank
99999 PNC bank
111 Mellon Bank
4 rows selected.
SQL> select refid, count(partner) from test5 group by refid;
REFID COUNT(PARTNER)
---------- --------------
111 1
99999 3
2 rows selected.
***********************************************
or how about this?
***********************************************
SQL> get fla
1 create or replace function fla
2 (n number)
3 return varchar2
4 is
5 cnt number;
6 retval varchar2(50);
7 begin
8 select count(partner) into cnt from test5 where refid=n;
9 if cnt > 1 then
10 retval:='has more than one partner';
11 end if;
12 return retval;
13* end;
SQL> /
Function created.
SQL> select refid,fla(refid) from test5;
REFID FLA(REFID)
---------- ------------------------------
99999 has more than one partner
99999 has more than one partner
99999 has more than one partner
111
4 rows selected.
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
