Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Issues in Lookup Transformation
Add to briefcase
13 posts, Page 1 of 2
12»»

Issues in Lookup Transformation Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
bhushan.bagul
Posted Monday, October 11, 2010 5:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:56 AM
Points: 25, Visits: 58

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
Post #1002074
Raunak Jhawar
Posted Monday, October 11, 2010 5:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:25 PM
Points: 946, Visits: 1,739
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
Post #1002088
bhushan.bagul
Posted Monday, October 11, 2010 6:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:56 AM
Points: 25, Visits: 58
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
Post #1002095
Raunak Jhawar
Posted Monday, October 11, 2010 6:10 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:25 PM
Points: 946, Visits: 1,739
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
Post #1002098
bhushan.bagul
Posted Monday, October 11, 2010 6:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:56 AM
Points: 25, Visits: 58
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
Post #1002107
Raunak Jhawar
Posted Monday, October 11, 2010 6:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:25 PM
Points: 946, Visits: 1,739
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
Post #1002126
bhushan.bagul
Posted Monday, October 11, 2010 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:56 AM
Points: 25, Visits: 58
Seems this Pivot will work for me. Thanks for you reply!
Post #1002137
steveb.
Posted Monday, October 11, 2010 7:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103
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.
Post #1002139
Raunak Jhawar
Posted Monday, October 11, 2010 7:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:25 PM
Points: 946, Visits: 1,739
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
Post #1002142
Koen Verbeeck
Posted Monday, October 11, 2010 7:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 9,370, Visits: 6,468
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1002159
« Prev Topic | Next Topic »

Add to briefcase
13 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.