Left outer Join result

[bobyon]

When doing a left outer join, what value is returned in a date type field from the right link for records that are not matched with the left?

My situation is that the only field in the table I am joining that is not part of the key is a date type field. I need to check in the following TX stage whether the row being processed matched in the join or not. What value should I test for?

(trying to simulate the reject feature of a lookup stage)

Thanks

[ray.wurlod]

It *should* return null, provided your target field is nullable. Otherwise it will expect that the target field has a default value and should return that.

[bobyon]

output column is marked as nullable but that's not what is being returned. It appears to be '0001-01-01', although I am not sure why. I suppose somehow that is the default value.

At any rate, I can test for that value and that *should* work.

[Sainath.Srinivasan]

Is the column defn in the output tab of join defined as nullable ?

If you need, you can add a new column using a column generator and set to a dummy value. This can be checked post-join.

[dxk9]

You can change the date field to string before join and then back to date/NULL post join depending on whether the data have value or not.

Regards,
Divya

[arnabdey]

Whatever values are fetched from the right table can be made to be nullable.. So when these columns are transferred to the output link of the join and the join fails, you will have null values in these fileds. In case you do not have any column to be fetched from right table you can add a hardcoded column with value=1 and nullable yes from the source. You can test nullability of this column as well.

[larzmcelroy]

If the column on the right is nullable you could use a coalesce statement within the SQL and provide a value to test for instead of checking for IsNull. The fact that it's returning a 0001-01-01 seems to indicate the field is defined as not null which makes this pointer pointless - Ha!

[bobyon]

I have confirmed that the date field mentioned(snd_dt) is marked as nullable=yes on the input and output columns of the join stage.

Let me describe my situation more completely:

The inputs to the joing stage are a seqential file on the left link and a Teradata Enterprise Stage feeding the right link. The join key is 8 fields, all of which are char except 1 (src_cd) which is defined as smallint in the TD table. Src_cd is defined as char in the seq file but passes through a TX stage to convert it implicitly (no function used) to convert it to smallint.

Other than the 8 join fields there is 1 additional field in the table, snd_dt. I am using that field to determine if the output record from the join is from an unmatched record. This is determined in a TX stage following the join where I (originally tested for null) now test for the value 0001-01-01. Those that have snd_dt = 0001-01-01 go to dataset "unmatched". Those that have snd_dt <> 0001-01-01 go to dataset "matched".

A strange thing happens on the way to the TX stage. For all the records that go to the "unmatched" dataset, 3 of the 8 join key fields have default like values. src_cd = 0, src_id = blanks and snd_dt = 0001-01-01.

Why? what happenned to the data that was in these fields before they entered the join. I have confirmed they did have values entering the join (via peeks and datasets) and that the src_cd and src_id on the output side of the join are derived from fields on the left link. The snd_dt is from the right link.

I've been on the phone with IBM all afternoon and now half the night trying to figure out this mystery. Any and all insight is appreciated.