Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: appending based on a 3rd table

    I have three tables:
    TABLE A = empty, waiting for data
    Assorted fields to match Table B

    TABLE B = Detail Data
    Assorted fields
    Category1 - can be empty
    Category2

    TABLE C = Dependent Data
    Category1 - can be empty
    Category2
    Group

    I am trying to get data from Table B into Table A based on a 2 Groups in C. The 2 groups should add up to the total in Table B - they are not.

    I have done something like this:
    Insert into A
    FIelds...
    From B
    Where (conditions from table B)
    AND EXISTS
    (select category1, category2
    From TableC SomeName
    where rtrim(TABLEB.Category1) = rtrim(SomeName.Category1)
    and rtrim(TABLEB.Category2) = rtrim(SomeName.Category2)
    and somename.group = 2);

    Even though this is giving me results, it isn't appearing to be correct.
    Is there something that is lacking in the logic, or a better way to do it perhaps?

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    My guess would be the nulls are throwing it off - rtrimming a null still results in null, and null != null.

    Maybe something like this?

    Code:
    Insert into A
    FIelds...
    From B
    Where (conditions from table B)
    AND EXISTS
    (select category1, category2
    From TableC SomeName
    where nvl(rtrim(TABLEB.Category1), 'X') = nvl(rtrim(SomeName.Category1), 'X')
    and rtrim(TABLEB.Category2) = rtrim(SomeName.Category2)
    and somename.group = 2)
    This way if category 1 is null in both tables, it will return the results assuming category 2's are equal b/c X=X. Give it a shot.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Thanks so much.
    Sounds like the answer. I did some preliminary queries and it's looking good.
    I originally thought that if a Category1 from each table was null, it would be smart enough to match them. No such luck.

    That's the second time in a very short time that I've had to use NVL().
    No matter what my next problem is, I'll try using NVL ().
    <grin>


    Thanks again.
    Jerry

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    You're welcome - nulls are tricky because they are not handled the way most people expect. Because nulls really don't exist in the eyes of the database, even if the other column is null, they can't be equal b/c it doesn't exist. Simple right?
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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