Left join user defined sql in Oracle EE stage

[vdr123]

While using oracle EE stage, my job is :
Oracle ee(read) --> Transform --> oracle ee(write)

In the (read) side, I have user defined sql, with left outer join (Tbl A left outer with Tbl B)

There are rows where I get nulls in Tbl B.

I get the following error :
Fatal Error: Attempt to setIsNull() on the accessor interfacing to non-nullable field "ORG_340b_GRANT_CODE"

Do I always have to use NVL on the columns coming from Tbl B in the user defined sql???
My fields are nullable from Tbl B

[ray.wurlod]

Somewhere in your design ORG_340b_GRANT_CODE column is defined as not nullable.

[vdr123]

I am also getting this when i use "VIEW data"
In OSH its defined as,
ORG_340B_ORG_340B_ID:nullable decimal[20,0]=ORG_340B_ORG_340B_ID;

I checked all the places where I am using that column - its nullable.

Is it holding on to something??? ( i closed many times/saved/exited)

I also went to table def and on the field properties, set it to use \123 as default if the incoming value is NULL.

Hope its not a bug? (V8.0.1)

Alternative can be to create view and use it. -- but this is basic thing of an ETL tool to default left outer joins without NVL

[ShaneMuir]

Are you doing anything in the transform stage to the field in question?

Generally, i have noticed taht when selecting fields from the db, the field will default to either the metadata as per oracle, or if you are creating a new field, as nullable.

So I would guess that the field in question is not nullable in the oracle table, and this seems to take precedent over whatever is set in the oracle stage itself (or it tries to do an implicit conversion), or in the transform you are trying to set a null value to that field in a variable?

[ArndW]

Could you could post your SQL join?

[vdr123]

Yes, oracle table field is non-nullable.(table mn_org_340b, NUMBER(20))
Oracle def is taking presidence. If I put a NVL it comes fine.
Imported metadata with orchestrate table def

sql is:
select
mm.REL_MARKERS as Mem_REL_MARKERS,
mb.org_340b_ID as ORG_340b_org_340b_id, mb.grant_code as ORG_340b_grant_code
from rms.mn_member mm
left outer join
rms.mn_org_340b mb --join 340b Grant for the latest Grant
on mm.member_id = mb.organization_id

[oracledba]

I had the exact same problem.

The reason datastage complains is:
In the data that field has null in the field it is complaining about. So as you did when you set that field to null, it runs fine.