Archives of the TeradataForum

Message Posted: Tue, 07 Sep 2004 @ 18:14:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi all,

I have found a sql problem which I can't explain myself.

I try to explain what I want to do:

I have two tables: bla1 (fact table) and bla2 (lookup table).

I want to join every row from bla1 with one row from bla2. The problem is, that I don't have allways a row (in bla2) for every row in bla1.

There are some (defined) rules to solve the problem: In bla2 for every combination (bdf,sai,ek_akt) there will be a genre 0. So if in bla1 genre is NULL then use 0. If genre is something else, but not in bla2.genre available then use 0 as genre.

I hope you understand what I mean. So, I made a sample as shown below:

     create table bla1 (
     bdf int
     ,sai int
     ,ek_akt int
     ,genre int)
     ;

     create table bla2 (
     bdf int
     ,sai int
     ,ek_akt int
     ,genre int)
     ;

     insert into bla1 (1,109,3,);
     insert into bla1 (1,109,3,0);
     insert into bla1 (1,109,3,4);
     insert into bla1 (1,109,3,6);

     insert into bla2 (1,109,3,0);
     insert into bla2 (1,109,3,4);

     sel * from bla1;
     sel * from bla2;

--The final select:

     sel * from bla1 a
     left join bla2 b
     on (a.bdf=b.bdf
     and a.sai=b.sai
     and a.ek_akt=b.ek_akt
     and a.genre=b.genre
     )
     or (a.bdf=b.bdf
     and a.sai=b.sai
     and a.ek_akt=b.ek_akt
     and 0=b.genre)
     ;

My base tables:

     bla1:
             bdf     sai     ek_akt  genre
             1       109     3       NULL
             1       109     3       0
             1       109     3       4
             1       109     3       6

     bla2:
             bdf     sai     ek_akt  genre
             1       109     3       0
             1       109     3       4

normal left join:

     bdf     sai     ek_akt  genre   bdf     sai     ek_akt  genre
     1       109     3       NULL    NULL    NULL    NULL    NULL
     1       109     3       0       1       109     3       0
     1       109     3       4       1       109     3       4
     1       109     3       6       NULL    NULL    NULL    NULL

all is fine and as expected.

Now with my rules:

     bdf     sai     ek_akt  genre   bdf     sai     ek_akt  genre
     1       109     3       NULL    1       109     3       0
     1       109     3       0       1       109     3       0
     1       109     3       4       1       109     3       0
     1       109     3       4       1       109     3       4
     1       109     3       6       1       109     3       0

Now I have the genre 4 two times. But why? Isn't the rule with an "OR" that if one part of the the OR-condition is true all is true and one row is selected? I expected the following:

     bdf     sai     ek_akt  genre   bdf     sai     ek_akt  genre
     1       109     3       NULL    1       109     3       0
     1       109     3       0       1       109     3       0
     1       109     3       4       1       109     3       4
     1       109     3       6       1       109     3       0

Perhaps I'm too tired today to find my logical bug? :-)

Hope someone can help me :-)

Thanks in advance,

Dirk

	<Prev	Next>		<<First	<Prev	Next>	Last>>