default value to -911

[hargun]

hi,

i am doing look up between two databases one is DB2 and other is SQL.

the column name GEO_1 is coming from DB2 database and doing look up with sourcesystem_value column with SQL database to get MD_id from SQL Database.

The requirement is that if look up failed then MD_id is hardcoded -911.In other words the req is given below also

1.keep null if what the source provides
2.MD_id;hardcode -911 if not found

i tried with the following way

Code: Select all

if isnotnull(GEO_1) then -911 else MD_id.

this give -911 only

if GEO_1 is null then keep null as source given but if GEO_1 have value and not found in look up then -911 harcoded.

Please help me on this.

[ray.wurlod]

If the lookup fails, then the returned value is NULL.

But you have tested it with IsNotNull(), which means that successful lookups will return -911 and failed lookups will return the returned value (i.e. NULL).

You needed to test using IsNull() or to reverse your logic:

Code: Select all

If IsNotNull(GEO_1) Then MD_id Else -911  

[hargun]

Thanks Ray for reply,

So i have to take like this

IF IsNull(GEO_1) then -911 else MD_id.

please reply.

[chulett]

Rather than wait for Ray to wander by again and reply, why not just... give it a try? That way you'll have something to talk about when he shows up.

[hargun]

i tried but no luck i tried with the follwing way also

Code: Select all

if isnull(DSLink166.GEO_1) then DSLink166.MdmId=' '  else if isnotnull( DSLink166.GEO_1) then DSLink166.MdmId=-911 else DSLink166.MdmId

but drppogn the records getting the error

Field MD_id is from input dateset '0' is null record dropped

[VineelPrem]

You could set the Lookup condition to CONTINUE . Then for the matched Lookups the Lookup key value( MD_id ) becomes 0 nad non matched lookups <> 0 . Then with a transformer filer the Nonlooks and set the MD_id =-911 .Funnel both the Lookups and nonlooks with a funnel stage

[ray.wurlod]

You cannot perform assignments in an expression. They end up being treated as Boolean expressions.

So, yes, you need

Code: Select all

If IsNull(DSLink166.GEO1) Then -911 Else DSLink166.MD1

as the derivation of output column MD1.

Or you could use

Code: Select all

If IsNotNull(DSLink166.GEO_1) Then DSLink166.MD1 Else -911

which is what I meant earlier by "reversing the logic".

[venkateshrupineni]

Hi,
As per my understanding of your requirement..
your requirement expected job flow like below

1) DB2 ->Lkp(<--- SQL) reference
in look up stage mentioned in the condition- look up failure value=continue
and read MD_id as nullable yes
2)then in the transformer write below derivation for those corresponding fields
GEO_1 --- Direct Mapping
MD_id -- NullToValue(MD_id,'-911') (req:if look up failed then MD_id is hardcoded -911).

Thanks,
Venky.