updating custom columns of target

[Former Member]

Hi

There are two columns in target table where the value as of now is 'Not Assigned'.

Market_Region = 'Not Assigned',

Market_Name = 'Not Assigned'

Now to these columns i would like to apply below set of update statement using BODS. If it is of one statement like below, i would do it using single lookup but for the multiple statements, what could be the way. Please advise.

--Country

UPDATE    POS_Processor

SET

       Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

       Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

       POS_Processor ON

             Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE

WHERE

       Disty_Electronic_CDF_Markets.State IS NULL AND

       Disty_Electronic_CDF_Markets.Zip IS NULL

--State

UPDATE    POS_Processor

SET

       Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

       Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

       POS_Processor ON

             Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

             Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15

WHERE

       Disty_Electronic_CDF_Markets.Zip IS NULL

       AND POS_Processor.CDF_15 IS NOT null

--Zip 3 - no state

UPDATE    POS_Processor

SET

       Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

       Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

       POS_Processor ON

             Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

             Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,3)

WHERE

       LEN(Disty_Electronic_CDF_Markets.Zip) = 3

       AND Disty_Electronic_CDF_Markets.State IS null

--Zip 1

UPDATE    POS_Processor

SET

       Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

       Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

       POS_Processor ON

             Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

             Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15 AND

             Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,1)

WHERE

       LEN(Disty_Electronic_CDF_Markets.Zip) = 1

       AND Disty_Electronic_CDF_Markets.State IS NOT NULL

--Zip 3

UPDATE    POS_Processor

SET

       Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

       Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

       POS_Processor ON

             Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

             Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15 AND

             Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,3)

WHERE

       LEN(Disty_Electronic_CDF_Markets.Zip) = 3

       AND Disty_Electronic_CDF_Markets.State IS NOT null

-- Zip 5

UPDATE    POS_Processor

SET

       Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

       Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

       POS_Processor ON

             Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

             Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15 AND

             Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,5)

WHERE

       LEN(Disty_Electronic_CDF_Markets.Zip) = 5

UPDATE    POS_Processor

SET

       Market_Name = Disty_Electronic_CDF_EMEA_Markets.EC_SHIP_TO_CTRY,

       Market_Region = Disty_Electronic_CDF_EMEA_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_EMEA_Markets INNER JOIN

       POS_Processor ON

             Disty_Electronic_CDF_EMEA_Markets.EC_SHIP_TO_CTRY = POS_Processor.EC_SHIPTO_COUNTRY_NAME

             AND Market_Name = 'Not Assigned' AND Market_Region = 'Not Assigned'

             AND POS_Processor.EC_REGION_DD1 = 'EMEA'

Thanks,

Abdulrasheed.

[former_member187605]

You need to build a complex mapping on Market_Name and Market_Region, using decode or ifthenelse function in combination with lookups.

decode (condition --Country, mapping --Country,

condition --State, mapping --State,

condition --Zip 3 - no state, mapping --Zip 3 - no state,

condition --Zip 1, mapping --Zip 1,

condition --Zip 3, mapping --Zip 3,

condition --Zip 5, mapping --Zip 5,

mapping -default)

[former_member208402]

Hi Shaik,

I don't think this is possible in single join/lookup since where conditions for each join condition is different.

Thanks,

Ravi kiran.

[Former Member]

Thanks for your response Ravi. is there any other way to achieve this.

[former_member208402]

I guess you can achieve this by using multiuple lookup/joins for each condition.

I prefer doing this with joins instead of looks since these conditions has where conditions like null filters.

Thanks,

Ravi kiran.