Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issues in Lookup Transformation


Issues in Lookup Transformation

Author
Message
bhushan.bagul
bhushan.bagul
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 80
I want to get column values depending on column data type and can use that values to insert in destination table.
For e.g. I have a lookup SQL where multiple records come for phone type (home, business, mobile) and in my destination table I have three columns like Home_phone, Business_phone and Mobile_phone. Now in my main SQL I have a colummn customer_key and also in lookup SQL. How can I get values for these Home_phone, Business_phone and Mobile_phone columns from lookup SQL using Customer_key?

Please let me know if you guys need more information for the same.

Thanks!
Bhushan
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1944
A join on these two tables would be more elegant based on Customer_key:-):-):-)

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
bhushan.bagul
bhushan.bagul
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 80
Thanks for your reply! But that will not solve my problem because in my main SQL I have only one record whereas in lookup SQL I have 3 records for phone types. An I don't want to insert duplicate records with different phone types in destination table thats why I have my destination table with 3 separate column for each phone type.
Did you get this?

Thanks!
Bhushan
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1944
Please provide some sample data to help us help you and what all till date have you tried?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
bhushan.bagul
bhushan.bagul
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 80
OLE DB Source SQL:

Select customer_key From customer (nolock)

customer_key
-------------
10001

Lookup SQL:

Select customer_key, phone_type, phone_number
From phone (nolock) as ph Join customer (nolock) as c
On ph.customer_key = c.customer_key

customer_key phone_type phone_number
---------------------------------------------
10001 Home 1234
10001 Business 5678
10001 Mobile 918239

Destination Table DDL:

Cust_Phone_Info
---------------------------------------------------------
customer_key home_phone business_phone mobile_phone
---------------------------------------------------------
10001 1234 5678 918239

This what I am expecting. Now if I join these 2 SQL then result will be three rows and I don't want to store in that way in my destination table.

I hope this will clarify what I needed.

Thanks!
Bhushan
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1944
bhushan.bagul (10/11/2010)
OLE DB Source SQL:

Select customer_key From customer (nolock)

customer_key
-------------
10001

Lookup SQL:

Select customer_key, phone_type, phone_number
From phone (nolock) as ph Join customer (nolock) as c
On ph.customer_key = c.customer_key

customer_key phone_type phone_number
---------------------------------------------
10001 Home 1234
10001 Business 5678
10001 Mobile 918239

Destination Table DDL:

Cust_Phone_Info
---------------------------------------------------------
customer_key home_phone business_phone mobile_phone
---------------------------------------------------------
10001 1234 5678 918239

This what I am expecting. Now if I join these 2 SQL then result will be three rows and I don't want to store in that way in my destination table.

I hope this will clarify what I needed.

Thanks!
Bhushan


Bhushan,

My approach:

Step 1: Use a pivot transformation for your Table 2 and transform it into as below:
{Key,Phone 1,Phone 2,Phone 3 }
Step 2: Use Join

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
bhushan.bagul
bhushan.bagul
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 80
Seems this Pivot will work for me. Thanks for you reply!
steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 Visits: 7195
Or if you want a pure SSIS solution you could opt for 3 sperate lookups
one for each for home, business, mobile
and then add each of these as a seperate column.
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1944
Happy to help. :-):-):-)

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
The pure T-SQL solution:


SELECT
    customer_key   = c.customer_key
   ,home_phone   = h.phone_number
   ,business_phone   = b.phone_number
   ,mobile_phone   = m.phone_number
FROM   customer c
   LEFT OUTER JOIN
   phone      h
      ON      c.customer_key   = h.customer_key
         AND   h.phone_type   = 'Home'
   LEFT OUTER JOIN
   phone      b
      ON      c.customer_key   = b.customer_key
         AND   b.phone_type   = 'Business'
   LEFT OUTER JOIN
   phone      m
      ON      c.customer_key   = m.customer_key
         AND   m.phone_type   = 'Mobile'



Beware, as this solution introduces NULL values for customers that don't have a phone number for a certain phone type.
I used LEFT OUTER JOINS, because it is possible for a customer to have a mobile phone and a home phone, but not a business phone, for example.

If a customer has multiple phone numbers for a specific phone type, you will still get multiple rows (e.g. a family having multiple mobile phone numbers). How are you planning to incorporate that in your solution?

A small side question: why do you use the nolock hint everywhere in you SQL statements? Is there a specific reason for?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search