[MattR]

I have a table which stores five important facts about a user:
ID (number)
First Name
Last Name
Type
Change_Indicator

The system, for some reason, stores maiden names of the female people in this table as well except the type = ‘ALMD’.

So, if I wanted to get a person’s maiden name I go:
SELECT Last_Name FROM person WHERE type = ‘ALMD’ and ID = 1234

Every time a person changes his or her details (any part of the name, etc.) a new row is inserted into the table and the old rows have a change_indicator flag set to ‘Y’.

So, if I have changed my name before and I want to get my latest record, I do:
SELECT * FROM person WHERE change_indicator IS NULL and ID = 12345

As a side note, the change_indicator for a maiden name is set to (something) (or other words NOT NULL).

The query I’m trying is to get all users and, if applicable, their maiden name.

Sounds like a perfect candidate for a self outer join, right?

Here is what I have:

SELECT 
  s.first_name,
  s.last_name as married_name,
  m.last_name as maiden_name
FROM 
  person s,
  person m
WHERE 
     s.change_indicator is null
 and s.id = m.id(+)
 and m.type_code = 'ALMD'
 and s.last_name != m.last_name

The last little s.last_name != m.last_name is because there can be duplicates (say if someone was once married and is now divorced their maiden name will match their last name, etc.).

Ok, there’s something wrong with that query. It’s not doing an outer join. Only women with maiden names are selected. Assuming the query is correct I think it has something to do with the way Oracle 8i handles the join parameters, but I can’t find anything online to tell me what to look for.


Help?

[andrewst]

Once you start outer joining a table, all joins and conditions for that table must take into account that it may be returning NULLs for a non-match. So neither of these conditions will work as is for someone with no maiden name:

and m.type_code = 'ALMD'
and s.last_name != m.last_name

This should work:

and m.type_code (+) = 'ALMD' -- definitely
and s.last_name != m.last_name (+) -- maybe

Though I'm not sure about (+) with != (and haven't got Oracle to hand to test it). If that doesn't work you could change the last condition to:

and (m.last_name is null or s.last_name != m.last_name)

[MattR]

Duh! *slaps forhead*

I totally forgot about that. When I get into work tomorrow I'll give it a try.

Thanks for the help!

[andrewsc]

I'm also working on a client database in Oracle, with versioning and a "most recent" flag which is the same as your "change_indicator". And, I also had prior Transact/SQL background, where the outer join operator would not apply when checkging against a constant.

There is an interesting performance issue here. For most of the time, the queries would like to pick up the most recent version (or at least start from there ). It is tempting to have a composite index on ( ID, Change_indicator ). The question is: will Oracle include into the index a record which has a null Change_indicator ?

Upon reading the documentation again, it seems to me that an ordinary B-tree index excludes null columns only if _all_ columns are null. This is not the case, since ID will never be null. Would anyone like to comment on this ?

In my database, I've side-stepped the issue, by using Y and N for my "most_recent" flag, but never null. I feel it's safer, since I'm relying heavily on that index.

[andrewst]

You are correct: Oracle only excludes rows from index where ALL indexed columns are NULL. You can check this for yourself like this:

SQL> create table t (a number, b number);

Table created.

SQL> create index tx on t (a,b);

Index created.

SQL> insert into t values (1,1);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
TX                                      1

SQL> insert into t values (2,null);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
TX                                      2

SQL> insert into t values (null,3);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
TX                                      3

SQL> insert into t values (null,null);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
TX                                      3

As you can see, only the last insert of (NULL,NULL) did not get stored in the index.

BTW: andrewsc/andrewst - this could get confusing!

[andrewsc]

>> andrewsc/andrewst - this could get confusing! <<

Thanks for pointing this out, it is an innocent coincidence.
It's not wise arguing with the moderator = .But then again, I'm using "andrewsc" for quite a while now, in all sorts of forums. E.g. http://www.oracle.com/forums/thread....98&q= #155898

For better clarity, I will add a signature.

BTW: who/why is sponsoring this forum ? Is there any commercial and/or community aspect to it ? I feel a vendor-neutral forum was long overdue in the Rdbms arena.

My best regards,

Andrew Schonberger
"andrewsc"
OTN member since Sep. 1998.

[MattR]

This is a privately run forum. Paul, the admin, is an Oracle DBA. I'm a Sybase ASE DBA, and you'll find many other DBAs and such here. Luckily we're without sponsorship from vendors.

[andrewst]

Originally posted by andrewsc
>> andrewsc/andrewst - this could get confusing! <<

Thanks for pointing this out, it is an innocent coincidence.
It's not wise arguing with the moderator = .But then again, I'm using "andrewsc" for quite a while now, in all sorts of forums. E.g. http://www.oracle.com/forums/thread....98&q= #155898

For better clarity, I will add a signature.

Hey, I just found it amusing! I wasn't suggesting you change it or anything. Mine actually stands for "Tony Andrews", but of course everybody thinks my first name is Andrew like you!