Problem with Union ALl

Problem with Union ALl

• Moses Kingsbury

  I need only 5 rows with out any null values (making sure that there is no null values in the source)
  
  when i combine ..iam getting ouput like
  
  ID FirstName LastName
  
  1 Null Null
  2 Null Null
  3 Null Null
  Null Kumar Null
  Null Jackson Null
  Null Tom Null
  Null Null Niranjan
  Null Null McDonald
  Null Null Harry
  
  But I want like this ..is there any solution
  
  ID FirstName LastName
  
  1 Kumar Niranjan
  2 Jackson McDonald
  3 Tom Harry
  
  
  I hope you understand what i want to say
  
  

• Limitx

  Sounds like you should be using the Merge Join transformation

  from BOL:

  The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin

  
  

• Kevin Gray

  That's what its supposed to do. What exactly is your requirement

  -Jamie

  
  
  

• Sniffy

  sorry small mistake . when using UnionAll getting output like this
  
  Like If each column has 5 rows ,iam getting (5+5+5+5)= 20 rows instaead of 5
  
  
  

• Jetpac1

  
  I can't use Merge Join Cynic ,Bcoz i have 4 inputs to combine...it will help only when your dealing with 2.
  To use Merge join for this scenario i have to sort 4 columns indvidually and then again i have to use 3 merge joins which will be heavy work
  
  
  

• Joshizzle

  Thanks Jamie for the hint ..got it solved with that idea.
  
  Thats what i need
  
  Thanks
  Niru
  
  
  

• hullracing

  Niru wrote:

  I need only 5 rows with out any null values (making sure that there is no null values in the source) when i combine ..iam getting ouput like ID FirstName LastName 1 Null Null 2 Null Null 3 Null Null Null Kumar Null Null Jackson Null Null Tom Null Null Null Niranjan Null Null McDonald Null Null Harry But I want like this ..is there any solution ID FirstName LastName 1 Kumar Niranjan 2 Jackson McDonald 3 Tom Harry I hope you understand what i want to say

  That makes no sense. For example, where is it defined that "1", "Kumar" & "Niranjan" form a single record Nowhere that I can see.

  Perhaps you are implying that the order in which the values appear is important. i.e. RecordX from dataset1 joins with RecordX from dataset2 and RecordX from dataset3. If this is the case you will have to give each row a number (here is how: http://www.sqlis.com/default.aspx 37) and then use the MERGE JOIN component.

  -Jamie

  
  
  
• Problem with Union ALl