Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Unanswered: Help with Oracle 8i Outer Join

    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:
    Code:
    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?
    Thanks,

    Matt

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help with Oracle 8i Outer Join

    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)

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Duh! *slaps forhead*

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

    Thanks for the help!
    Thanks,

    Matt

  4. #4
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    null included into B-tree ( was 8i Outer Join)

    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.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: null included into B-tree ( was 8i Outer Join)

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

    Code:
    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!

  6. #6
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Re: null included into B-tree ( was 8i Outer Join)

    >> 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....5898&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.

  7. #7
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Thanks,

    Matt

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: null included into B-tree ( was 8i Outer Join)

    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....5898&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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •