December 2, 2008 at 7:32 am
Can anyone see an problems with the beloew code?
The tables it is using to populate tbldeals are temp tables.
The insert seems to insert the data correct but duplicates it over 20 times
So im exspecting 100 rows and I get 2000... :ermm:
INSERT INTO dbo.tblDeals(dbo.tblDeals.Client,dbo.tblDeals.Bought_Sold,dbo.tblDeals.RIC,
dbo.tblDeals.Trade_Date,dbo.tblDeals.Price,dbo.tblDeals.Counterparty_Code,
dbo.tblDeals.Dealer,dbo.tblDeals.Commission,dbo.tblDeals.Consideration,
dbo.tblDeals.Quantity,dbo.tblDeals.Currency,dbo.tblDeals.SEDOL,
dbo.tblDeals.Participant_ID,dbo.tblDeals.FIDESSA_Ref,
dbo.tblDeals.FX_Rate,dbo.tblDeals.Local_Commission,
dbo.tblDeals.Bgn_Cond1,dbo.tblDeals.Bgn_Cond2,
dbo.tblDeals.Bgn_Cond3,dbo.tblDeals.Bgn_Cond4,
dbo.tblDeals.Dealing_Capacity,dbo.tblDeals.RisklessPrincipal,
dbo.tblDeals.Sector,dbo.tblDeals.ClientID,dbo.tblDeals.MarketMaking)
SELECT dbo.Bargains.Client,dbo.Bargains.BoughtSold,dbo.Bargains.RIC,
dbo.Bargains.Trade_Date,dbo.Bargains.Price,
dbo.Bargains.Counterparty_Code,dbo.Bargains.Dealer,
dbo.Bargains.Commission,dbo.Bargains.Consideration,
dbo.Bargains.Quantity,dbo.Bargains.Currency,dbo.Bargains.SEDOL,
dbo.Bargains.Participant_ID,dbo.Bargains.FIDESSA_Ref,dbo.Bargains.FX_Rate,
dbo.Bargains.Local_Commission,dbo.Bargains.Bgn_Cond1,dbo.Bargains.Bgn_Cond2,
dbo.Bargains.Bgn_Cond3,dbo.Bargains.Bgn_Cond4,dbo.Bargains.Dealing_Capacity,
dbo.Bargains.Riskless_Principal,dbo.Bargains.MarketSector, dbo.Bargains.ClientID,
isnull(dbo.tblStocks.MarketMaking,0)
FROM dbo.Bargains
left join tblStocks
on Bargains.SEDOL=tblStocks.SEDOL
Thanks All
December 2, 2008 at 7:43 am
pri.amin (12/2/2008)
Can anyone see an problems with the beloew code?The tables it is using to populate tbldeals are temp tables.
The insert seems to insert the data correct but duplicates it over 20 times
So im exspecting 100 rows and I get 2000... :ermm:
INSERT INTO dbo.tblDeals(dbo.tblDeals.Client,dbo.tblDeals.Bought_Sold,dbo.tblDeals.RIC,dbo.tblDeals.Trade_Date,dbo.tblDeals.Price,dbo.tblDeals.Counterparty_Code,
dbo.tblDeals.Dealer,dbo.tblDeals.Commission,dbo.tblDeals.Consideration,
dbo.tblDeals.Quantity,dbo.tblDeals.Currency,dbo.tblDeals.SEDOL,
dbo.tblDeals.Participant_ID,dbo.tblDeals.FIDESSA_Ref,
dbo.tblDeals.FX_Rate,dbo.tblDeals.Local_Commission,
dbo.tblDeals.Bgn_Cond1,dbo.tblDeals.Bgn_Cond2,
dbo.tblDeals.Bgn_Cond3,dbo.tblDeals.Bgn_Cond4,
dbo.tblDeals.Dealing_Capacity,dbo.tblDeals.RisklessPrincipal,
dbo.tblDeals.Sector,dbo.tblDeals.ClientID,dbo.tblDeals.MarketMaking)
SELECT dbo.Bargains.Client,dbo.Bargains.BoughtSold,dbo.Bargains.RIC,
dbo.Bargains.Trade_Date,dbo.Bargains.Price,
dbo.Bargains.Counterparty_Code,dbo.Bargains.Dealer,
dbo.Bargains.Commission,dbo.Bargains.Consideration,
dbo.Bargains.Quantity,dbo.Bargains.Currency,dbo.Bargains.SEDOL,
dbo.Bargains.Participant_ID,dbo.Bargains.FIDESSA_Ref,dbo.Bargains.FX_Rate,
dbo.Bargains.Local_Commission,dbo.Bargains.Bgn_Cond1,dbo.Bargains.Bgn_Cond2,
dbo.Bargains.Bgn_Cond3,dbo.Bargains.Bgn_Cond4,dbo.Bargains.Dealing_Capacity,
dbo.Bargains.Riskless_Principal,dbo.Bargains.MarketSector, dbo.Bargains.ClientID,
isnull(dbo.tblStocks.MarketMaking,0)
FROM dbo.Bargains
left join tblStocks
on Bargains.SEDOL=tblStocks.SEDOL
Thanks All
My gut feeling without looking at the data tells me you my have more than a one to one relationship between the tables Bargains and tblStocks. I'd start by looking at the select query itself and the data.
December 2, 2008 at 7:48 am
My gut feeling without looking at the data tells me you my have more than a one to one relationship between the tables Bargains and tblStocks. I'd start by looking at the select query itself and the data.
Thank for the reply,
I ran the 'Select' part of the above code and as you suggested it returned the 2000 rows of data. So this could be my problem.
Would it help to show you some example data?
What else would help you?
Thanks for your time.
December 2, 2008 at 7:54 am
Not only sample data, but also the DDL for the tables. Please read the article referenced in my signature block about asking for help to see how best to post that information to get you the best help.
December 3, 2008 at 12:38 am
Ok I think I have now included some more helpful information.
I have attched:
- 'Bargains' tables colums & data types
- 'procImportAllFiles' SP in fill (saved as a .txt but can be opened as .sql)
- 'bargains csv.xls' (please rename to .csv) copy of the flat file which is imported using my SSIS package to the temp tables
Please let me know if you require any further information.
Thanks again for your time.
December 4, 2008 at 8:49 am
What about the data in table tblStocks ?
(First, I strongly suggest you lose the habit of prefixing table names with "tbl" -- It does nothing to help understand the code).
Left say you have 500 records in the first table and that for each record in table1 you have, in table2 an average of 4 records pertaining the same record in table1.
[font="Courier New"]
Table1
SEDO Item_Name
1 Kosmo
2 Cramer
3 Elaine
Table2
SEDO Location Qty
1 A1 100
1 A2 200
1 A3 300
2 B1 50
2 B2 150
2 B3 250
2 B4 350
2 B5 450
[/font]
The query will produce the following set
[font="Courier New"]
SEDO Item_Name Location Qty
1 Kosmo A1 100
1 Kosmo A2 200
1 Kosmo A3 300
2 Cramer B1 50
2 Cramer B2 150
2 Cramer B3 250
2 Cramer B4 350
2 Cramer B5 450
3 Elaine NULL NULL
[/font]
So even though you had only three records in table1, joining with table2 WILL result in
9 records...
As suggested by others, review your data in BOTH tables to see if there are multiple lines in table2 pertaining to the same record in table1 AND ** WORST ** if there are multiple records in table1 having the same SEDO number.
Regards
Just what is it you want exactly ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply