Merge Join Or Union All
Oct 9, 2006
Hi,
I am trying to normalize data using the unpivot transform. I have to unpivot using more than one key so I have a multicast feeding into two unpivot transforms then into a sort transform. This is where my problem starts - I have tried using a Merge Join (inner Join) transform but dont get the expected result.
My original data looks like this:
Pk_ID
Choice1
Choice2
Feedback1
Feedback2
10
a
b
x
y
After the mulitcast - unpivot - Merge Join, the expected result is: (pk_newID is an identity)
Pk_newID
fk_ID
Choice
Feedback
563
10
a
x
564
10
b
y
However with a Merge-Join (inner join on pk_ID) I get
Pk_newID
fk_ID
Choice
Feedback
563
10
a
x
564
10
a
y
565
10
b
x
566
10
b
y
Is the Merge Join transform not the right choice?
Thanks
View 2 Replies
ADVERTISEMENT
Apr 17, 2008
I am using SSIS to load the data from 7 access files into a table in SQL2005.
My problem:
the data in the 7 access files can be duplicate.To identify a record we use Company + Contact attributes.
we use a timestamp in each access file.
I want to load the data in a table and separate the duplicates in another in order to work on these later .
What DATA FLOW TRASFORMATIONS should I use??
How to configure it?
Any help is appreciate.
Thank you
mcrisf
View 5 Replies
View Related
May 22, 2015
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
View 2 Replies
View Related
Aug 10, 2007
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
View 1 Replies
View Related
Jun 5, 2006
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
View 3 Replies
View Related
Nov 7, 2006
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2
But how to do this in SSIS?
Thanks - Ken
View 2 Replies
View Related
May 6, 2008
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS
View 3 Replies
View Related
Jan 10, 2007
Hi there SQLTEAM
I have a problem, and need your help.
table1 has 1 single field,example
pkiTownID
DATA
1
2
3
4
table2 has 1 single field, example
pkiTownID
DATA
6
7
8
9
What SQL Query should i run to merge or join these 2 tables into 1
The output that i would like it the following
DATA
1 6
2 7
3 8
9 9
Is this possible?
View 6 Replies
View Related
Aug 7, 2007
I am trying to merge the selected contents of 2 tables by using a date field in both tables.
For example, in the AdventureWorks database of VS2005, I would like 3 columns: loginID, modifieddate and addressline1. Where the row has come from the Employee table, the address field should be blank, so its not really a join that I want (at least I dont think so).
This query only returns 2 colums:
select loginid, modifieddate from HumanResources.Employee union all select addressLine1, modifiedDate from Person.Address
Is this possible, or do I need an additional common column that can be inner joined on the 2 tables?
Can anyone help?
View 2 Replies
View Related
Dec 7, 2006
Ok, getting a little better at joins, but now need to take it two more steps.Main table tblEquipmentsFields: ID, EquipmentType, Location, StatusSecond Table tblEquipments_GroupFields: equipmentID, categoryID, subCategoryID (all are ints)I joined these two together and got the info back I needed:SELECT a.ID, a.Description, a.Status, a.Location, from tblEquipments ainner join tblEquipments_Group U on u.categoryID = a.EquipmentTypeand u.subCategoryID = @type Now, the a.status returns a number. What the status actually is is in tblStatus.Fields: statusID (int) , Status (varchar).So, the statusID matches a.Status.I need to get the text from tblStatus in the field Status and replace the a.Status (which is a number still) with the text.The other one, location, is the same situation.I've been trying to do another join and am at an end on this.Suggestions?Thanks!!Zath
View 2 Replies
View Related
Sep 17, 2007
Hi,
I have three tables named as BroadCastetails,PaymetMaster,MemberMaster.
I have two query as mentioned below--
SELECT MemberMaster.FirstName, MemberMaster.LastName, BroadCastDetails.BroadCastName FROM BroadCastDetails INNER JOIN MemberMaster ON 5 = MemberMaster.MemberID AND BroadcastCreationDateTime between '01/01/2007' AND '12/12/2007'
SELECT BroadCastDetails.ScheduledStartDateTime, BroadCastDetails.TotalCalls FROM BroadCastDetails UNION SELECT PaymentTransaction.TransactionDate, PaymentTransaction.CallsToCredit FROM PaymentTransaction
I want the result of the above two query in a GridView.How can I do that ? Its urgent...
View 2 Replies
View Related
Nov 18, 2003
Hi,
I have an Orders Table and a Freeshipping Table.
ORDERS
======
CustomerId OrderDate Quantity
========== ========= ========
1000 01/01/2003 5
1000 01/04/2003 9
1000 01/08/2003 14
2000 01/01/2003 4
1000 06/03/2003 9
4000 05/02/2003 4
FREESHIPPING
=============
CustomerID FreeDate
========== ========
1000 01/01/2003
1000 01/03/2003
How can I write a query that will return the following result to show the order details for customerid 1000
for a date range between 01/01/2003 and 01/08/2003
OrderDate Quantity FreeShipping
========= ======== ============
01/01/2003 5 Y
01/03/2003 0 Y
01/04/2003 9 N
01/08/2003 14 N
Note that even if an order was not placed and the date was a freeshipping date, it is still displayed in the resultset
Thanks in advance,
-ron
View 2 Replies
View Related
Oct 3, 2007
hi
can any one help me to solve this question?
View 5 Replies
View Related
Apr 15, 2008
Simple request and I am going nuts!
Simple Explanation:
I have a table with numbers 1-5, I want to left join a table with numbers 1-10, and show a sum... So since numbers 6-10 will not match anything, I will have a sum of zero for those rows. Simple, I thought!
SELECT SUM(ISNULL(RI.quantity,0)) AS Quantity, usr.conversionRate, usr.CorpType
FROM ( SELECT 0 AS conversionRate, 1 AS CorpType UNION ALL SELECT .05, 1 UNION ALL SELECT .5, 1 UNION ALL SELECT 1, 1 UNION ALL
SELECT 1.5, 1 UNION ALL SELECT 1.75, 1 ID UNION ALL SELECT 2, 1 UNION ALL SELECT 0, 2 UNION ALL
SELECT .05, 2 UNION ALL SELECT .5, 2 UNION ALL SELECT 1, 2 UNION ALL SELECT 1.5, 2 UNION ALL
SELECT 1.75, 2 ID UNION ALL SELECT 2, 2 ) usr
LEFT OUTER JOIN Items I
ON I.ConversionRate = usr.conversionRate
LEFT OUTER JOIN Requests_Items RI
ON I.ID = RI.itemID
AND ISNULL(RI.impactMonth,RI.dueDate) >= '02/01/2008' and ISNULL(RI.impactMonth,RI.dueDate) < '03/01/2008'
LEFT OUTER JOIN Corporations_Requests CR
ON CR.requestID = RI.requestID
LEFT OUTER JOIN Corporations C
ON C.CorpID = CR.corporationID
AND C.CorpType = usr.CorpType
GROUP BY usr.CorpType, usr.conversionRate
ORDER BY usr.CorpType, usr.conversionRate
I have no clue why this doesn't work. It appears that the UNION ALL(s) are being crossed joined. Any light shed on this would be greatly appreciated!
View 4 Replies
View Related
Jan 27, 2008
hi, i'm a newbie and trying to form a query for hours
any help appreciated.
Tables
Persons(ID,Name)
Oranges(PersonsID,Quantity)
Apples (PersonsID,Quantity)
i need to see total numbers of oranges and apples of each person on persons table, for example
personsid name sumoforanges sumofapples
1 lisa 60 20
2 mary 0 15
3 jane 15 0
4 tom 0 0
thanks...
View 3 Replies
View Related
Aug 5, 2005
Hallo !I have 2 tables with similar construction.DataOLD and DataNEW.In my grid I need a JOINED table.On the one hand I need all the data from the old table and the datafrom then new table with the same id (left outer join works)But additionally I need all rows from the new table where no equivalentrows are in the old table.example:TableOLDdiameter amountOLD20 10050 20030 300TableNEWdiameter amountNEW20 50060 60070 120Wished Result:diameter amountOLD amoutNEW20 100 50050 200 NULL30 300 NULL60 NULL 60070 NULL 120Who can help me?Many Thanks.aaapaul
View 6 Replies
View Related
Aug 8, 2006
I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5and table B has field 1,2,3,4,5. I want to do a union on these. (I havedone so successfully if I stop here) I also want to join table C whichhas field 1,6,7,8,9. I would like to join on field 1 and bring in theother fields. I can join table C to A or B. I can union table A and Bbut I do not know how to both union A and B then join C. Can someoneplease help me? Thanks in advance.
View 7 Replies
View Related
Nov 28, 2007
Can i do a Full outer join on a union? and if so how?
I have this stored procedure. but then i was notified that they want to show all the sales reps, even if they dont have visits, and all the activities, even if neither has values. Right now this is what it shows:
Sales Rep. Visits Vacation Sick
Bob Smith 2 5 0
Julie Troy 1 0 1
Stefani Gray 1 0 0
--------------
I want it to look like this
Sales Rep. Visits Vacation Sick Drive-with Seminar Office-day Meeting
Bob Smith 2 5 0 0 0 0 0
Tod Taylor 0 0 0 0 0 0 0
Matt Mathews 0 0 0 0 0 0 0
Julie troy 1 0 1 0 0 0 0
Stefani Gray 1 0 0 0 0 0 0
heres my code:
Code Block
ALTER PROCEDURE [dbo].[PROC_DAILY_ACTIVITY]
(@Region_Key int=null)
AS
BEGIN
SELECT
NULL as Progress_time,
NULL As completed_time,
NULL as Dial_in_date,
NULL AS Customer_Code,
Non_Customer_Activities.question_code,
Non_Customer_Activities.description,
Sales_Group.Region,
Sales_Group.Name AS Territory_Name,
Non_Customer_Activities.Que_Desc AS Store_Name,
Non_Customer_Activities.Logged_Time AS TheDate,
CONVERT(FLOAT,Non_Customer_Activities.Logged_Time) Float_Date,
Non_Customer_Activities.response AS Response,
NULL AS is_Visit_Fg
FROM Qry_Sales_Group AS Sales_Group INNER JOIN
(SELECT QH.question_code, Question_Header.description, CONVERT(datetime, DATEADD(day, QH.cycle_day - 1, P.start_date), 6) AS Logged_Time,
SUBSTRING(QH.entity_code, 1, 5) AS SR_Code, QH.response, Territory_In_Sales_Responsible.Territory_Code AS SR_Territory_Code,
'Not Customer Related' AS Que_Desc
FROM question_history AS QH INNER JOIN
period AS P ON P.period_code = QH.period_code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON
Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code = SUBSTRING(QH.entity_code, 1, 5) COLLATE Latin1_General_CI_AS INNER
JOIN
questions AS Question_Header ON Question_Header.question_code = QH.question_code
WHERE (QH.entity_code LIKE '%.USER%') AND (Question_Header.question_code IN ('AME01', 'ASE01', 'ACO01', 'ALU01', 'AOS01', 'APH01',
'ATR01', 'ATE01', 'ACR06', 'ACR05', 'ACR02', 'ACR03', 'ACR08', 'ACR07')) AND (CONVERT(datetime, DATEADD(day, QH.cycle_day - 1,
P.start_date), 6) = case
when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3
else datediff(dd,0,GetDate()) - 1
end )) AS Non_Customer_Activities
ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code
WHERE Region_Key=@Region_Key
UNION ALL
SELECT
in_progress_time,
completed_time ,
dial_in_date,
Customer_Activities.Customer_Code,
NULL AS Expr1,
NULL AS Expr2,
Sales_Group.Region,
Sales_Group.Name AS Territory_Name,
Customer_Activities.Customer_Name AS Store_Name,
Customer_Activities.Logged_Time AS TheDate,
CONVERT(FLOAT,Customer_Activities.Logged_Time) Float_Date,
NULL AS Response,
Customer_Activities.Is_Visit_Fg
FROM Qry_Sales_Group AS Sales_Group INNER JOIN
(SELECT DISTINCT in_progress_time,completed_time,dial_in_date,
V.visit_date AS Logged_Time, Customer_Code, customer_name AS Customer_Name,
Territory_In_Sales_Responsible.Territory_Code AS Cust_Territory_Code, 1 AS Is_Visit_Fg
FROM Qry_visits AS V
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON
Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code = V.sales_person_code COLLATE Latin1_General_CI_AS
INNER JOIN FSSRC.dbo.Communication_statistics as Communication
ON Communication.sales_person_code=Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
WHERE (CONVERT(datetime, CONVERT(Varchar, V.visit_date, 110)) = case
when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3
else datediff(dd,0,GetDate()) - 1
end )
) AS Customer_Activities ON
Sales_Group.Code = Customer_Activities.Cust_Territory_Code
WHERE Region_Key=@Region_Key
end
View 5 Replies
View Related
Apr 2, 2008
I have got the following union statement:
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr,
FROM plan2008
GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
FROM fsp_auftrag
GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
Thanks in advance!
View 8 Replies
View Related
Jul 30, 2015
I have a query which wants to union join the data. no matter how many times I tried, I got an error. How to change my union query?
select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT
(Select distinct b.lev5
from bf_data a
inner join bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
[Code] .....
View 9 Replies
View Related
Feb 19, 2008
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0
) AS p
GROUP BY p.id
View 9 Replies
View Related
Aug 5, 2015
Struggling to come up with an overall sum for each individual user when using the below code
SELECT
Practice.ibvStaffCategorisation.StaffId
,Practice.ibvStaffTotalsCL2Y.Period
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
[Code] ....
As I am using union all it shows two values for each user, the problem is I am getting the data via lookup like below
=Lookup(Fields!StaffId.Value, Fields!StaffId.Value, Fields!Sum_ChargeableAmount.Value, "ABSTimeRollingY")
I don't think I can use a Sum within a lookup so at the moment it is only bringing through the first value for each user but I would like it to bring through both.
Is there a way to do this within the dataset or via the query in the table?
View 2 Replies
View Related
Sep 9, 2004
Hello All,
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)
I am trying to run another query on my first query results so I don't mess my first query up. Here it is:
SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";
Please Help! Thank you in advance.
--------------------
5 minutes away from pulling my last one!
BaldNAskewed
View 7 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Apr 26, 2004
I have got a query in which a merge join is 99% of the cost .... and I am confused ... is not merge join supposed to be the fastest ??? Anyone seen this before ???
Any ideas why this could be happening ... and sorry ... do not ask me to post the code coz I will not be able to ...
View 14 Replies
View Related
Mar 3, 2008
Hi, all experts here,
Any advices for when will be a better way of using Merge join instead of other options?
Thank you very much and I am looking forward to hearing from you shortly.
Best regards,
View 2 Replies
View Related
May 5, 2006
All,
I need to use Merge Join transformation to join two sources. One is from a PIVOT transformation and one of the output columns is ISSORTED, the other is from an OLE DB Source using a query. The Merge Join transformation requires both input source have to be sorted. I cannot find the ISSORTED property on the OLE DB Source!!
I tried to use Derived/ copy transformations but cannot find the property also. How can set the OLE query sorted in order to use the MergeJoin?
Thanks a lot
View 4 Replies
View Related
Jun 15, 2007
Hello all,
I have a package where I use merge join for two sorted inputs and the output is stored in a raw file.
In another package, the raw file from above package is again merge joined with another sorted input. Now my question is....do we need to sort again the raw file from first package? or is it OK to set the isSorted property to True and define the sort keys?
Thank you.
View 5 Replies
View Related
Dec 17, 2007
I am new to this SSIS.
I have a simple join query like this
select a.id from tbl_a a, tbl_b b where a.id = b.id and I want insert the result to my temp table.
the query results is 1500 rows.
but when I use merge join in SSIS, it only inserts to my temp table 4 rows.
I use inner join and I already set the IsSorted to true and specify the sort position for the columns in both source tables
In tbl_a, there are one million rows, in tbl_b, there are 2000 rows.
I don't know why the merge join cannot work out my task.Is there other way that I can just run this simple join query in SSIS to copy the data?
Please help, thanks in advance.
View 6 Replies
View Related
Apr 30, 2008
Hello,
I have a Merge Join transformation and when i sort values in OLEDB source the merge join fails, but if i use a sort transformation it works! Why??
Best regards,
Fred
View 5 Replies
View Related
Sep 29, 2006
Hi,
I have a SQL Statatment:
SELECT * FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X
AND A.Y= B.Y
When i execute this code in sql server returns 549 lines. I created a package with two oledb sources one for each table, sorted the tables with fields X and Y after placed a Merge Join with the fields:
A.Y join B.Y order 1
A.X join B.X order 2
both fields with the Join Key checked
But my package return 411 lines.
What's happened?? :(
When a i have the code:
SELECT A.X, A.Y, B.X, B.Y
FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X
When i did the join only one field SSIS worked fine, sql server returns 622 and SSIS returns 622 lines.
Please help-me...
Thanks,
André
View 8 Replies
View Related
Nov 21, 2006
Hi guys! I'm trying to figure out how to join 3 tables, but I can't seem to find a solution. What I want to do is to put table 1, table 2 and table 3 into table_merged.
table_merged = table 1 + table 2 + table 3
Is it possible to merge tables even if they have different fields?
Please help.
Onegai shimasu...
Thanks in advance!
View 1 Replies
View Related
