Lookup Stage not matching conditions

[alisoviejo]

I have a situation where if the source rows match the lookup rows then need to push 1 as the result ,else as 2.


In the lookup stage conditions if I use continue for both the columns "Condition Not Met" and " Lookup Failure " , it says, if the source records DO NOT MATCH the lookup records the default value will be used or sent to target.

My question is how do you set a default value for the specific row (It is a Non-nullable, Varchar row, tried to look in edit rows box)

Thanks for the ideas.

[ray.wurlod]

Default values are not set in a Lookup Stage. There are lots of other stages where it can be done, for example a Modify stage using the handle_null() function.

[alisoviejo]

Hi Ray,

Hey finally got to see you on this website!
So I have a Oracel Stage then a lookup Stage and now do I need to add another modify stage (or a transformer) after the look up stage. In the modify stage I can set the default value if it is null , but the look up stage writes NULL so the field is as such not null !

I guess I need to use a transformer, let me know if I am on the right path !

[alisoviejo]

My job desing is :

OracleStage
|
|
Source (Oracle)--->LookUpStage----->DataSet


If the source row doesnt match , it is just returning a space and I have it as not nullable and continue option in the lookup stage constraints.
How can I get it to insert the word NULL if the lookup fails !
Thanks

[ameyvaidya]

On lookup failure, the default value for an non nullable varchar field is empty string "".

I don't remember if the modify stage has a function for converting empty strings, but a transformer will convert this to a null.

the derivation for this would be:

Code: Select all

 if in.col="" then setNull() else in.col

you will also need to set the nullability of this field to "Yes" and switch RCP off.

[alisoviejo]

Hi,
I made the field nullable and now it rops the column with the error NullHandler,0: Field 'CDE' from input dataset '0' is NULL. Record dropped.

I am searching the forum, looks like there are few postings.
Thanks,

[ameyvaidya]

Hi Aliso,

Need a clarification:

What output do you need if the lookup fails. Is it "2" or the null value or the string "NULL" ?

[alisoviejo]

If the lookup fails I need 2 as output.

If lookup doesnt fail then 1, if lookup fails then 2.

Thanks,

[ameyvaidya]

Ok,

In that case, undo this step i had mentioned:

you will also need to set the nullability of this field to "Yes" and switch RCP off.

In a transformer, set the derivation of the necessary output column(new or existing) to:

Code: Select all

if in.col="" then 2 else 1

in.col is the lookup reference varchar non nullable column.

[alisoviejo]

Hi,
Thanks for the time, I have set the field to Yes for the "Nullable" column. Now I write the output to a copy stage and after which I have a transformer where i do the following

If In.Col='NULL' then '2' else '1'

It just drops the column !

APT_CombinedOperatorController,0: Field 'RR' from input dataset '0' is NULL. Record dropped.

[ameyvaidya]

you can set the field to non-nullable.


I'd recommended the nullable option becuse of:

How can I get it to insert the word NULL if the lookup fails !