.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Removing columns after Merge Join ?

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server


I have the scenario (using a third party component) where certain columns must not be present as input.

I am doing a Full Outer - Merge Join.

This results in:



but now in some cases the LeftPK may be  null in other cases the RightPK may be null,

so i'm creating a derived column to check:

ISNULL(LeftPK) ? RightPK : LeftPK

and call this the TruePK

this all works however now i'm left with 3 columns

LeftPK; RightPK; TruePK

Is there some kind of transform (other than script task) that i can use to get rid of Left and Right PK columns before sending input to next third party component?

I.W Coetzer

View Complete Post

More Related Resource Links

repeating merge joins using full outer join - should be possible without sort, but cannot get the co

Hello,   I am trying to combine several source tables with Merge Join.  For the Moment I have three source tables all already sorted in the source query and the is sorted property with the correct keys set. (its an aggregate query with group by and sort by the key columns) Then I do a merge join between two of the tables - with a full outer join. Then I just want to do another merge join with the output of the next result. but as the merge join do not combine the key columns into one column I need to add a derived column which substitute null values on the left side of the join with values from the right side of the join. The second merge then says only the left columns of the first merge join is sorted, but not the comined columns. If i Replace with a derived column the left side primary key columsn with the combinded key columns the next merge join says the input is not sorrted? But the output must be sorted. So how do I get the next merge join working without the ssis sort transformation? the data is sorted, but the component does not recognize the sorted data? I do not want to use (can use) the sort because there are flowing >= 500 Mio. records through the pipeline. Any help appreciated.   Hanneshttp://www.hmayer.net/

Merge the repeat columns

The Form has repeating grid. I publish the form as an administrator approved form and the columns of the repeating grid with the 'merge' function.Yet the columns are promoted as single line of text insead of mulitple lines of text in the sharepoint form library.How to solve this ?

Merge Join: Full Outer Join - keep key values in case of no-match

I'm using the Merge Join to join several different incoming flows into one flow.  I've configured the joins to use a Full Outer Join because I need all records from all sources. In the case of a no-match, I want the component to keep the values of join key fields instead of setting them to NULL.  How can I achieve that?  (Activating the checkbox doesn't help, because that adds a new field to the output instead of re-using the existing one.)

Problem with Merge Join and condition splt trasnformations in SSIS 2008

  Hey, While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details... Condition Split and Merge Join and Condition Split: Please check the below diagram(not really just tried) for the complete details. Just FYI data is in sorted order   condition Split -> 420000      | 18                                                          | 419982  Merge Join(left)  -- src1(6000)      |10  Condition Split      |9     |1   the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows. But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split. Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.       Merge: We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor

Comparing columns with NULL values--Merge says unmatched when data is matched.

I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse


TABLE 1SELECT I.STUNA    ,S.SNO         ,SUM(B.AMT * Y.YEAR) AS AMT  FROM STUDENT S  LEFT JOIN INFO I ON S.SNO = I.SNO  LEFT JOIN BONUS B ON S.SNO = B.SSNO  LEFT JOIN YEAR  Y ON S.SNO = Y.SSNO  STUNA       SNO      AMTJOHN         A        10LISA         B        20ALLEN        C       100TABLE 2SNO   AMTA     1B     2C     3D     5I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)STUNA       SNO      AMTJOHN         A        11LISA         B        22ALLEN        C       103EDDIE        D         5I TRIED USE THIS QEURY SELECT I.STUNA    ,S.SNO         ,SUM(B

Unable to Merge columns outside Group ? Unnecessary Row height ?

HI , In a matrix tablix , I'm unable to merge columns outside the group . Heres a quick screen shot . http://img843.imageshack.us/img843/366/samplema.png The top 3 columns is what i meant [ Unable to Merge columns outside Group ]  and Row height of the first row ? Thanks in Advance !!Rajkumar Yelugu

Removing columns inherited from parent not working

I'm trying to install a couple of custom workflow task content types that are based on the standard workflow task type, but I want to remove some of the attributes defined on the std worfklow task type.  Per the MSDN online info you can use the <RemoveFieldRef/> tag in your XML content type definition file to remove such columns.  For some reason mine does not appear to work, however.  In addition to not removing the columns specified by the <RemoveFieldRef/> tags, it also does not create my custom "Comments" column -- It DOES create the custom "Respoinse" column. The following are my XML definition files for the feature (which installs fine and the content types are created, but the columns I want to remove still show up).  Any idea what I'm missing to remove these columns from the custom types?  I pulled the IDs for the columns I want to remove from the std type definition XML under the 12-hive FEATURES folder Type Definition File <?xml version="1.0" encoding="utf-8"?> <!-- See this blog for specifics: http://www.scribd.com/doc/2278897/Part-4-Custom-Workflow-Forms-TaskEdit-Form --> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <ContentType ID="0x010801003D9E4ED01F024d28AF0056E2654CECB0" Name="MoreInfo Task" Group="

Merge Columns Like Excel In Gridview


Hello everybody.

I have a excel file which is in structure a class routine:

                  | 9-10 | 10-11 | 11-12 | 12-01 | 01-02 | 02-03 | 03-04 | 04-05 |

tue             |           subject1                      |     subject2     |    subject3      |

wed            |   subject2     |                subject3                   |    subject1      |

thu             |            subject3        |     subject2     |             subject1          

Merge Join vs. T-Sql Inner Join?



I noticed this has been posted before - but could not get it working with the information on that post so here goes:

I have a T-Sql query that INNER JOINs 2 tables on one field - this returns 6,383 records.

(the one table contains 6,383 records and the other table 12 records)

However when I try to implement a data flow task with two data sources (one for each table) and sort the and then try to use the Merge Join transformation to sort and join them on the exact same field the result is 11,073 records.

What is going on here? logically this does not make sense to me ...

I.W Coetzer

Merge Join Failue


I'm working with SQL 2008 SSIS.  I'm trying to join two tables on email fields.  For test purposes I have copied some of the email addresses from table A to table B to insue I have matching values.  The OLE DB Source components each use a SQL query that sorts the output by the email addresses in ascending order.   For each of the two source components I've set the IsSorted property to True and set the Output Columns SortKeyPosition property to 1.  Within the SQL query I've also added trim functions to eliminate any possible extra spaces.  Since for testing purposes table B's email addresses are copies of email addresses in table A there is no issue with case.   When linking the two outputs using the Merge Join flow component I do not get any matches.

I've tried sorting using a Sort component rather than within the OLD DB Source query, but it seems to lock up on one of my flows (about 1.5 million records) and won't proceed.

At this point I don't know what else to try. 

How to merge columns in DetailsView control?


I have a DetailsView control with two columns and 7 rows. I need the two columns to merge in row two and get rid of the header. I only the data from the DB to display no the header name in row two. How do I merge it in DetailsView control?

Help is much appreciated.

Removing unwanted spaces in columns

How to remove unwanted spaces empty lines in table columns

Merge join and IsSorted for joining more than 2 inputs


I have an XML file like this:


 <e1 myvalue="test">



   <detail value1="hello" value2="world" />

   <detail value1="

join two identity columns


I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.
Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.

Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

Merge join in SSIS

how to use merge join in SSIS?

How to merge two columns of a table with SSIS



how to merge two columns of a table using SSIS.




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend