I am creating a simple table with 3 columns (first name, last name and id columns). I need to return each distinct id in the table, along with a single name associated with the id (it doesn't matter which name I get back, as long as I get back a valid name). The example below has two distinct names for each id. I am using functions to get the first name and last name out of the table (each has its own function). Each function uses the rownum pseudocolumn.
Is it possible to retrieve invalid names? For instance, in the example below, when retrieving a name for id 1, is there anyway to retrieve Tom for the first name and Howard for the last name? Or, because this is all contained within a single query / transaction, could I only retrieve Tom Jones or Shirley Howard?
Jan
create table test (fnm varchar2(30), lnm varchar2(30), id number);
insert into test values ('Tom','Jones',1);
insert into test values ('Shirley','Howard',1);
insert into test values ('Sue','Snue',2);
insert into test values ('Jay','Buckley',2);
insert into test values ('Robert','Smith',3);
insert into test values ('Russel','Weiss',3);
create function getfnm (passedinid in number)
return varchar2
is
first varchar2(30);
begin
select test.fnm into first
from test
where id=passedinid and rownum=1;
return first;
end;
/
create function getlnm (passedinid in number)
return varchar2
is
last varchar2(30);
begin
select test.lnm into last
from test
where id=passedinid and rownum=1;
return last;
end;
/
select distinct(id), getfnm(id) first, getlnm(id) last from test;
ID FIRST LAST
---------- --------------- ---------------
1 Tom Jones
2 Sue Snue
3 Robert Smith
that would be the wrongest way in the world to do this.
That would be a bad use of rownum - it is highly unlikely it would cause the issue you mention in this particular case, but ROWNUM is a psuedo column assigned to rows as they flow out of a query, a result set. It is not something assigned permanently to a row.
but the big problem in your example is the use of DISTINCT. Your query is the same as:
select distinct *
from (select id, getfnm(id), getlnm(id) from test )
/
distinct (or UNIQUE) works on the "set", it is NOT a function.... that could cause some real problems (performance pops into my head immediately)
There is also a huge issue with read consistency to consider.
But first, we should NOT be using plsql here at all!!! there are much better approaches, for example:
ops$tkyte%ORA10GR2> select id, fnm, lnm
2 from (select test.*, row_number() over (partition by id order by lnm) rn
3 from test
4 )
5 where rn = 1;
ID FNM LNM
---------- ------------------------------ ------------------------------
1 Shirley Howard
2 Jay Buckley
3 Robert Smith
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id, max( fnm || ' ' || lnm ) name
2 from test
3 group by id;
ID NAME
---------- ------------------------------
1 Tom Jones
2 Sue Snue
3 Russel Weiss
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id,
2 min(fnm) KEEP (dense_rank first order by rowid) fnm,
3 min(lnm) KEEP (dense_rank first order by rowid) lnm
4 from test
5 group by id;
ID FNM LNM
---------- ------------------------------ ------------------------------
1 Tom Jones
2 Sue Snue
3 Robert Smith
Any of those would be preferable (well, more than preferable, they return correct consistent results)
let me address that read consistency comment. Each of the SQL statements you execute will be read consistent by default as of the time they are executed. In a single user system (what is that?) you would be OK probably (safe enough) with your query, but as soon as we introduce more than one user and modifications - your approach is suspect.
Into your functions, I'll inject some "sql" and an autonomous transaction. This autonomous transaction just represents other users in your system doing "stuff" and committing as your query is executing:
ops$tkyte%ORA10GR2> create or replace function getfnm (passedinid in number) return varchar2
2 is
3 pragma autonomous_transaction;
4 first varchar2(30);
5 begin
6 select test.fnm into first
7 from test
8 where id=passedinid
9 and rownum=1;
10 delete from test where id = passedinid;
11 commit;
12 return first;
13 end;
14 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function getlnm (passedinid in number) return varchar2
2 is
3 pragma autonomous_transaction;
4 last varchar2(30);
5 begin
6 select test.lnm into last
7 from test
8 where id=passedinid
9 and rownum=1;
10 delete from test where id = passedinid;
11 commit;
12 return last;
13 end;
14 /
Function created.
So, as the function is called, we'll delete some data.... And commit. the outcome - interesting:
ops$tkyte%ORA10GR2> select distinct id, getfnm(id) first, getlnm(id) last
2 from test
3 order by id;
ID FIRST LAST
---------- ---------- ----------
1 Tom
1
2 Sue
2
3 Robert
3
6 rows selected.hmm....