Row_number With Distinct And Inner Join Messes Up Results
Jul 28, 2007
I want to select all usernames from tlbUsers which practice a certain sport.
I also have a table UsersAndSports:
UserID int
SportID int
I therefore need an inner join statement:
select username from
(SELECT ROW_NUMBER() OVER (ORDER BY au.LastActivityDate DESC) as RowNum,
ud.username from tblusers us
inner join tblUsersAndSports uas on uas.usercode=us.usercode and (uas.sportID=3 or uas.sportID=4)
WHERE us.UserName <>''
)as MemberInfo WHERE RowNum between (@startRowIndex+1) AND (@startRowIndex+@maximumRows)
The problem lies in the usage of the ROW_NUMBER command. I need that in order to effectively page through my records, but it also makes that
I cant make a distinct selection as each RowNum is unique....:S
In case the user would practice 2 sports, the query would return 2 rows...
if I place a distinct in front of the username as such:
select distinct username from
The query would return each user only once...BUTBUTBUT: my startrowindex and maximumrows apply to the results that can be found in the MemberInfo selection..
so lets say my startrowindex would be 0 and maximumrow 5
if my 1st query (without distinct) was to return:
john
john
john
john
mike
mike
rob
my 2nd query (with distinct) would return:
john
mike
Hello, first post since I can usually find answers by reading the forums. I've searched the internet up and down and for some reason I can't get this query to work properly.
I have two tables:
ticket
ticket_id ticket_to ticket_from
message
message_id ticket_id message
There can be several messages per ticket, connected by the ticket_id. I'd like to only pull only the most recent message, as well as the results from the ticket table. Currently I'm getting all messages per ticket.
Here's what I have:
SELECT distinct ticket.ticket_to, ticket.ticket_from, message.ticket_id, message.message FROM tickets INNER JOIN message ON tickets.ticket_id = message.ticket_id GROUP BY message.ticket_id, message, ticket_to, ticket_from
Any help would be greatly appreciated! Thanks much.
This is gonna be a quite long explanation of an understanding problem and I seriously hope that anyone can help me out here. Please look at the following example: use tempdb; create table T1 ( C1 varchar(80) not null default 'Empty' ,C2 uniqueidentifier not null default newid() ) go -- Add some rows set nocount on insert T1(C1) values('A') go 5 insert T1(C1) values('X') go 7 insert T1(C1) values('Y') go 9 insert T1(C1) values('Y') go 6 Now run the following two queries and include the actual execution plan: select distinct top 10 row_number() over(order by C1), C1 from T1
select top 10 row_number() over(order by C1), C1 from T1 You€˜ll get two different plans with the first one slightly more expansive than the second. What I do not understand is, why the optimizer does not ignore the DISTINCT in the first case. When I include a ROW_NUMBER() without partitioning (or, to e exact: with only one partition) as in the above query, every row will get a unique number, so all returned rows are already distinct, aren€™t they? But as this optimization possibility is so obvious and simple, I don€™t believe that the optimizer is wrong €“ rather I suppose that I€™m the on, who does not understand what€™s going on here. If you play around with some SELECTs, the difference between DISTINCT included and excluded can be very noticable. Take the following example: select distinct top 10 row_number() over(order by a.C1), a.C1, b.C1 from T1 as a inner join T1 as b on b.C2 = a.C2
select top 10 row_number() over(order by a.C1), a.C1, b.C1 from T1 as a inner join T1 as b on b.C2 = a.C2 where the (unnecessary?) DISTINCT in the first query holds responsible for 34% oft he total query costs, making the cost for the first query over twice as much as for the second. I€™ve tried to find at least one example where DISTINCT makes sense €“ but without success. In all my experiments (included the above, of course), always the same resultset is returned, regardless of DISTINCT or not. The problem has been detected using an OR-mapper (nHibernate), where the SQL code is automatically generated. Inside the code generation process, a ROW_NUMBER() columnn without partitioning is always added, as well as in many cases also DISTINCT. I€™d simply like to remove the DISTINCT keyword from the code generation, because it increases the performance dramatically in many cases. But fort he reasons mentioned above, I€™m not sure whether I can do this without risk. Any ideas are greatly appreciated.
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library. I get this error when i used into storeprocedure : ROW_NUMBER() function is not recognized in store procedure. i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library. I need to add that function into MS SQL SERVER 2005 database library. Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name] from Contact right join Account on Contact.[Account Name] = Account.[Account Name] where Contact.[Account Name] = Account.[Account Name]
Here's one thats had me and a coworker puzzled. Hopefully it's something simple:
TABLE A: (log of messages) userid, int date, datetime message, varchar(50)
Table A Data: 1, 6/18/2008 @ 2:32:41, This is my message 1, 6/18/2008 @ 2:31:02, This is my message 1, 6/17/2008 @ 7:34:26, This is another message 2, 6/18/2008 @ 2:32:41, This is not his message 2, 6/16/2008 @ 11:21:32, This is my message
TABLE B: (List of users) userid, int name, varchar(100)
Table B Data: 1, John 2, Mike
I want to extract the most recent message logged per user, i.e.:
name | date | message --------|---------------------|------------------------- John | 6/18/2008 @ 2:32:41 | This is my message Mike | 6/18/2008 @ 2:32:41 | This is not his message.
I have been unable to come up with a query that can return just the one value. I've tried variants of:
SELECT DISTINCT b.name, a.date, a.message FROM a INNER JOIN b ON a.userid = b.userid
including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary?
I have 2 tables. Product, ProductCategory. Product table consists of (productid, productname, producttypeid, createdate, portid) productcategory consists of (productid, categoryid, productcategoryid, categoryid, prodroletypeid, createdate) I want to write sql query to get the unique products. So the count should only give one value/resuls. But when I run the following query, I am getting more than one rows select count(distinct categoryid) as uniquecat from productcategory as pc, product as p where pc.prodroletypeid in ('P', 'F') and pc.createdate <= dateadd(d, 30, getdate()) and p.portid = 100 group by pc.productid
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows. thanks for any suggestions...
~ select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nowo where nowo.wasrst <='40' union select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nopo where nopo.wasrst <='499'
I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.
Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.
I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.
SELECT * FROM SAMPLE WHERE ret_ref_no in ( SELECT ret_ref_no FROM SAMPLE GROUP BY ret_ref_no HAVING COUNT(*) > 1 )
I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:
SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index FROM dbo.csr_standards_cmsars ORDER BY ARSFamily
 but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups).  The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?
I have a view which joins 2 tables together in a left outer join.
Now, the left outer join table (lets call this SalesData) may or may not have records. As months and years go by, records will be inserted or updated.
I am trying to find a query which will do the following:
Bring back all records where the branch matches an input value and also:
- bring back NULL records for the YR and MO column - bring back values which match the input params for YR and MO column.
this works as expected:
quote: SELECT wmtecp.* FROM v_stores wmtecp RIGHT OUTER JOIN v_Stores roj ON wmtecp.ID = roj.ID WHERE wmtecp.TM = @p1 AND 1=1 AND roj.YR IS NULL AND roj.MO IS NULL OR wmtecp.MO = 11 AND wmtecp.YR = 2014
This is right outer joining the view to itself and prefer it this way.
Here is the thing: I want to bring back only distinct records (distinct by store ID I guess).
We are bringing back result set which contains NULL and NON null values but for both result sets the same storeID is returned, I do not want this but to only bring back the same record once either WITH values or WITHOUT values.
Hi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig
UID , Name, amount, start date                      End Date     1     A        10        2015-05-01 00:00:00            2015-05-01 23:59:59     2     A        10        2015-05-02 00:00:00            2015-05-02 23:59:59     3     A        10        2015-05-03 00:00:00            2015-05-03 23:59:59     4     A        10        2015-05-04 00:00:00            2015-05-04 23:59:59      5     B        10        2015-05-05 00:00:00            2015-05-05 23:59:59
Hi again, I have this SQL (part of a stored procedure) where I do LEFT JOIN. SELECT callingPartyNumber, AlertingName, originalCalledPartyNumber, finalCalledPartyNumber, dateTimeConnect, dateTimeDisconnect, CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration, clientMatterCode
FROM CDR1.dbo.CallDetailRecord t1 LEFT JOIN CDR2.dbo.NumPlan t2 ON t1.callingPartyNumber=t2.DNorPattern
WHERE (t1.callingPartyNumber LIKE ISNULL(@callingPartyNumber, t1.callingPartyNumber) + '%') AND (t1.originalCalledPartyNumber LIKE ISNULL(@originalCalledPartyNumber, t1.originalCalledPartyNumber) + '%') AND (t1.finalCalledPartyNumber LIKE ISNULL(@finalCalledPartyNumber, t1.finalCalledPartyNumber) + '%') AND (t1.clientMatterCode LIKE ISNULL(@clientMatterCode, t1.clientMatterCode) + '%') AND (@callerName is NULL OR t2.AlertingName LIKE '%' + @callerName + '%') AND (t1.duration >= @theDuration) AND ((t1.datetimeConnect) >= ISNULL(convert(bigint, datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), t1.datetimeConnect)) AND ((t1.dateTimeDisconnect) <= ISNULL(convert(bigint, datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), t1.dateTimeDisconnect)) The problem is that if the t2 has more than one entry for the same DNorPattern, it pulls the record more than once. So say t1 has a callingPartyNumber = 1000. t2 has two records for this number. It will pull it more than once. How do I get the Unique value. What I am trying to get is the AlertingName (name of the caller) field value from t2 based on DNorPattern (which is the phone number). If this is not clear, please let me know. Thanks, Bullpit
SELECT first.name,first.country, second.name, second.string FROM first LEFT OUTER JOIN second ON first.name = second.name WHERE first.date='2015/02/24'
This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table.
I've been trying to get this to work right. The db table has 3 fields: id, vwr, and reqType. I need all DISTINCT vwr's from the table. (vwr's can repeat) This gives me all rows, not distinct... select distinct d.id, d.vwr, d.reqType from tblVWR AS dinner join tblVWR ton d.vwr = t.vwr Any suggestions? Thanks, Zath
Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL;
"SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID"
Which seems to work to an extent in that I do get three articles in the same Category appearing. However, there are sometimes duplicates appearing, so I need to incorporate a DISTINCT clause to the above. I'm not sure where to put this in though. Any ideas?
I am writing a script to create a audit trigger on any table. I am getting duplicate rows inserted into my audit table, only for the primary key columns. Anybody see why?
Right now I am debugging an Insert, so I think you can ignore the "U" update part of the Where clause.
....starts with other code to determine columns and primary key fields for selected table....
--get number of columns select @rowId = min(RowId), @MaxRowId = max(RowId) from #tblFieldNames
-- Loop through fields and build Sql string while @RowId <= @MaxRowId BEGIN SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId
i have a query with some inner joins that return information about an employee
i am running into a problem in that i need one employees email based on one inner join and anothers based on another inner join -- is there a way to get both in one query?
select employee.email , bunch of other stuff from work
INNER JOIN technician ON business_location.technician_id = technician.technician_id
inner join salesrep on business_location.salesrepid = salesrep.salesrep_id
not sure how to get both of those to employee.emp_id and get the email
Table 3 holds the persons or groups to whom we have given a copy of a specific part.
PersonsCopiedAt pca_id integer primarey key pca_wpd_id integer foreing key from WordPartDoc pca_pg_id integer this is the id of the table that holds the persons or the id of the table that holds the group pca_p_or_g char(1) this is a flag : 'P' for a person, 'G' for a group
Now, here is my problem... I have created the following sql query select wd_id, wpd_id from wordDoc inner join wordPartDoc on wpd_wd_id = wd_id inner join PersonCopiedAt on pca_wpd_id = wpd_id where pca_pg_id = someID and pca_p_or_g = 'G' --or 'P'
This works perfectly if a pca_pg_id is provided, but this is provided using a combobox... If the user doesn't select a person - and thus - no id is provided - inner join brings me as many records of wpd_id as the number of persons that were given a copy. I understand that this is the correct behaviour but in the later case (when no id is provided) i want only one record to be returned. Is there a way to do so ? do i have to redisign the db schema ?
I have been using T-SQL for a while now although the majority of my work required relativley simple queries. I just need to know is there a way to JOIN the results of several SELECT queries, maybe through the use of functions??
A reference to any online article would be most helpful.
Trying to cross-update 2 tables. Picture a checkbook reconcilliation without common check numbers. The checkbook has uniqueids and the bank has transaction ids but they are different. So the match is on date/payee and amount. So I wrote 2 checks to the same person, on the same day, for the same amount but forgot to enter one in the register.
when i run the update statement: update b set b.bankid=c.myid from checks c join bank b on c.cdate=b.cdate and c.payee=b.payee and c.cost=b.cost
Both bank statement records would be updated to my one check record [can't happen] Also: this will be running on a hundred thousand records per month with potential for duplication/ommission on either side.
What's a poor newbie missing?? I'm doing something similar on a lesser volume by running sequential statements through an ASP script but performance is poor. I know SQL can do this, just not how to approach it.
I have two tables, one with data for one type of ID (call it key1) and a table where this ID (call it key2) is transformed to another. It is not one-to-one match with these types of ID and I want to check those key2 cases that have two or more key1 linked to it.
It is simple enough and for the easiest check I don't even need table1 to run it as table2 has both key1 and key2 variables.
However, not all doubles are of identical worth. Table1 (that has only key1) has a year variable. I am interested in doubles that have same year variable, ie. in table1 there are two key1 cases with the same year variable that are linked to one key2 case in table2.
So in essence in table1 I have key1, year and in table2 I have key1, key2 and I am interested in those key2-cases that have more than one key1 linked to it where years are the same.
SELECT query.key2 FROM ( SELECT DISTINCT a.key1, b.key2 FROM table1 AS a JOIN table2 AS b ON a.key1=b.key1 JOIN table1 AS c ON a.key1=c.key1 WHERE a.year=c.year) AS query GROUP BY query.key2 HAVING COUNT(*)>1
I tried it joining table1 twice and fiddling around with various JOIN and WHERE clauses (the one on show being the simplest and most naive one) but the query still returns key2-doubles whose key1 cases are linked to different years. It is simple enough if you give a distinct year value in where clause (and drop second table1 join as unnecessary) but I don't want to go through all years manually one by one. I was thinking some kind of iterative loop that changes the value of the year in where clause could do the trick (and be heavy computationally) but I don't really know how to go around doing it, haven't done any loops in SQL ever.
Hi, I have three tables: 1 Order Details 2 Product Info 3 Received Qty
The query is pretty simple however I'm getting incorrect results.
The query grabs a load of information (including a SUM statement) from the order details tbl.. this is INNER JOIN ed to the products tbl to match an ID field which grabs some extra info from the product info tbl.
all fairly straightforward and works without a problem..
However I now need to add in the received qty tbl (as the name suggests this keep a track of the qty's of items received)
I need to join this tbl to the order details tbl based on a unique ID from the orders tbl and then SUM together all of the entries in that received qty tbl for that item.
Result is I get a single record which shows the qty ordered (from the order details tbl), and the total received (from the received qty) tbl
These are held in a seperate tbl as an order can be deliveryed in several stages.
Here is my statement:
SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem, dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived, SUM(dbo.Suppliers_POs_Details.Qty * dbo.Suppliers_POs_Details.PricePerItem) AS LineTotal, SUM(dbo.Suppliers_POs_Details_Received.Qty) AS QtyReceived FROM dbo.Suppliers_POs_Details INNER JOIN PDS_Login.Supplier_Products ON dbo.Suppliers_POs_Details.ProductID = PDS_Login.Supplier_Products.ProductID LEFT OUTER JOIN dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID WHERE (dbo.Suppliers_POs_Details.POID = @POID) GROUP BY dbo.Suppliers_POs_Details_Received.Qty, PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem, dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived, dbo.Suppliers_POs_Details_Received.Qty
The problem is, if there is more than one relating record in teh Received Qty tbl instead of that being added together as I want, two results appear...
It's obviously something simple but I can't seem to figure it out - anyone point me in the right direction?
I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1 FROM employee LEFT OUTER JOIN request AS request ON employee.emp_id = request.emp_id WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE())) AS dayspending FROM employee AS employee_1 LEFT OUTER JOIN request AS request_1 ON employee_1.emp_id = request_1.emp_id WHERE (employee_1.emp_id = @emp_id) GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial, employee_1.emp_lname
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours FROM dbo.tblEventStaffRequired;
and SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked FROM tblStaffBookings GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
Hi, I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.
However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.
Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.
Here's the query to reproduce this error.
Code Snippetcreate table t1 (pk1 int not null,primary key (pk1)) create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1)) go insert into t1 values (1) insert into t2 values (2, 'XXXXX') go create view V as select pk1, 'ZZZZ' as label1 from t2 go select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1 from t1 as A left outer join V as B on A.pk1 = B.pk1 go
This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".
I just want to confirm that this is an issue and no fix is available for this so far.
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode FROM TriggerTable FOR XML PATH(''), TYPE ).value('(./text())[1]', 'VARCHAR(MAX)') ,1,2,'')
Hi I have a strange issue here. Both the input datasets of the merge join are sorted . Everything works fine if the dataset doesn't contain speacial characters like - (hipen). This is the sorted dataset.ASI311_3ASI311_3ASI312ASI311ASIKit1ASIKit1
Merge Joiner has to look for ASI311 and finds it in the list and everything works as expected. But if the dataset is changed to
ASI311_3ASI311_3ASI-312ASI311ASIKit1ASIKit1
Then it doesn't find the ASI311 in the above dataset ? Only change between the two datasets is the - (hipen) which is added in one of the field. Does SSIS has a differenent precedence of the special characters than that of the Database. Both the datasets are on SQLServer.