December 1, 2008 at 3:38 am
Morning All,
Setup
- SSIS importing 3 files into 3 temp tables
- SP transfers the data from the temp tables to 2 other tables then clears down the temp tables
Problem
The SP seems to be only copying half the data into dbo.p&l and copying into dbo.deals up to 37 times
Below are bits of the SP:
UPDATE dbo.Bargains SET Riskless_Principal = 1 WHERE Riskless_Principal = 'RP'
UPDATE dbo.Bargains SET Riskless_Principal = 0 WHERE Riskless_Principal = ''
UPDATE dbo.Bargains SET FX_Rate = 1 WHERE Riskless_Principal = ''
UPDATE dbo.Bargains SET FX_Rate = 1 WHERE Riskless_Principal = 0
UPDATE dbo.Bargains SET MarketSector = 'Unk' WHERE MarketSector Not In(Select MarketSectorNew FROM tblFTSESector)
UPDATE dbo.bargains set marketsector = dbo.tblFTSESector.shortDescription FROM dbo.Bargains INNER JOIN dbo.tblStocks ON dbo.Bargains.ISIN = dbo.tblStocks.ISIN INNER JOIN dbo.tblFTSESector ON dbo.tblStocks.MarketSector = dbo.tblFTSESector.MarketSectorNew
UPDATE dbo.bargains set marketsector='Unk' Where marketsector is null
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
UPDATE dbo.tblStocks SET MarketMaking = 0 WHERE (dbo.tblStocks.SEDOL <> '0000002')
UPDATE dbo.tblStocks SET MarketMaking = 1 FROM dbo.house RIGHT OUTER JOIN dbo.tblStocks ON dbo.house.ISIN_CODE = dbo.tblStocks.ISIN WHERE (dbo.house.ISIN_CODE <> '')
Any help is much appreciated
December 3, 2008 at 7:36 am
I don't see an insert into the p&l table anywhere in the code you provided.
It would appear that your join between tblBargains and tblStocks is returning multiple rows because you have more
than one match on the SEDOL column. Just run the select to see the numbers being returned. You can likely "fix"
this with a group by or distinct on the select.
Please see the links in my signature to see how to post to get better answers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply