Page 1 of 1
default value to -911
Posted: Tue Jul 30, 2013 2:22 pm
by 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.
Posted: Tue Jul 30, 2013 4:41 pm
by 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
Posted: Wed Jul 31, 2013 7:12 am
by hargun
Thanks Ray for reply,
So i have to take like this
IF IsNull(GEO_1) then -911 else MD_id.
please reply.
Posted: Wed Jul 31, 2013 7:29 am
by 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.
Posted: Wed Jul 31, 2013 2:01 pm
by 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
Re: default value to -911
Posted: Wed Jul 31, 2013 4:40 pm
by 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
Posted: Wed Jul 31, 2013 4:47 pm
by 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".
Do left outer join in look up stage
Posted: Thu Aug 01, 2013 3:06 am
by 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.