Joiner stage - how ds behave on outer join attribute values

[muralisankarr]

Hi,

The joiner stage is dumping a junk values on the rows that test due to left or right outer joins.

Code: Select all

<removed by Moderator>

If you can have a look at the first and last record, it is evident that datastage is generating zero on some numeric field (in case of last record for employee id it was zeros and for salary it was null), junk on date/time and empty or NULL on varchar column. Please help to understand the behavior.

In most of the case, I have used a joiner stage set to do left outer operation and on down stream operation I find the inner join records (in away trying to capture the reject record out of joiner stage). In this case if datastage is dumping some junk value how can I handle this? Also a junk value in a date time field means on any downstream date time operation I have to check the validity of the date/time. I have used Oracle as the source. Please help to decode the way datastage give the output values on outer joins

Many Thanks
Murali Sankar R

[muralisankarr]

I have done a quick test on look up and Merge stage. It also exhibits the same kind of behavior. Please find the metadata below

Code: Select all

record
(
  EMPLOYEE_ID:decimal[6,0];
  FIRST_NAME:nullable string[max=20];
  LAST_NAME:string[max=25];
  EMAIL:string[max=25];
  PHONE_NUMBER:nullable string[max=20];
  HIRE_DATE:timestamp;
  JOB_ID:string[max=10];
  SALARY:nullable decimal[8,2];
  COMMISSION:nullable decimal[2,2];
  MANAGER_ID:nullable decimal[6,0];
  DEPARTMENT_ID:nullable decimal[4,0];
)
record
(
  DEPARTMENT_ID:decimal[4,0];
  DEPARTMENT_NAME:string[max=30];
  MANAGER_ID:nullable decimal[6,0];
  LOCATION_ID:nullable decimal[4,0];
)

[Sainath.Srinivasan]

That may be because those columns are non-nullable. So datastage sets them to default values for corresponding datatypes.

Make all fields as nullable after join stage and you will see correct values.

[kris007]

Datastage defaults not nullable Integer fields to 0 whenever you use a Join (left outer or right outer) or LookUp stage and the data is coming from the non-master link or reference link.

It is always a good practice to use dummy/masked values when you provide examples rather than copying the data from your work place. The information you have provided in the example is sensitive and I would recommend you re-edit the post to mask your examples.

[chulett]

I've removed your code tagged example data due to concerns over exposing PHI/PII in the forums here. Please feel free to edit your post and replace your previous examples with something more appropriate - redacted / masked or completely made up values for example - for things like name, phone number, salary, etc.

Thanks.

[muralisankarr]

I've removed your code tagged example data due to concerns over exposing PHI/PII in the forums here. Please feel free to edit your post and replace your previous examples with something more appropriate - redacted / masked or completely made up values for example - for things like name, phone number, salary, etc.

Thanks.

Thanks all. The data I have pasted is the employee and departments data out of standard oracle scott schema tables. It is not a real data!

[chulett]

Good to know and now that we know that, feel free to edit your post and replace it. I sent you what I removed in a personal message so you shouldn't even have to dig it up again.