Problem with query, involving INNER HASH JOIN

Hi all,
I've got 2 problems with the following statement, which leads me to
believe there is a bug somewhere.
This weekend we upgraded from SQL7 to SQL2000 (SP3) and it all went
smoothly except for this statement below 'losing' a couple of records.
It is contained in a stored procedure (believe me you don't want to
see it) and gives me 2 problems.
1. It seems depending on the parameters passed which affect the volume
of records being processed all the records either come through or not.
Now I KNOW the record is in ALWAYS in #TEMP1 (don't question it is as
I'm sure) as I debug it just before the insert. When I change the temp
table to a permanent table and run the raw SQL through QA using this
permanent table it always gets inserted and never loses the record,
hence I know it's not the join leading to the record not coming
through
2. Now the strange thing is if I add 'INNER HASH JOIN' to the 2 joins
then the record is lost but 8 records do come through, but if I just
make it a 'JOIN' then absolutely nothing comes through. Why is this?
How come removing the 'INNER HASH' reduce the number of records?
I know this sounds a bit of a ramble but I've spent several hours
investigating this and still have no idea. What I was wondering is has
anyone had any problems when upgrading with joins/temp tables/stored
procs somehow not selecting all the records expected?
SQL:
INSERT INTO #TEMP2(Day, Year, DataSource, StartDate,
MaturityDate, BusinessDayDate, DealID, TradeNum, Flowsign, Currency,
CurrencyUnit, ReportingCurrency,InstrumentType, ChangeType, FXRate,
SetNumber)
SELECT DISTINCT
@.StartDate,
DATEPART(Year,@.BusinessDayDate),
TRB.DataSource,
@.StartDate,
TP.MaturityDate,
TRB.BusinessDate,
TRB.DealNo,
TRB.TradeNo,
TRB.Flowsign,
TP.CurrencyCode,
TRB.CurrencyUnit,
@.RepCurrency,
TRB.Instrument,
TP.ChangeType,
ER.Rate,
1
FROM TCH_gt_TradeProfile TP
-- remove 'INNER HASH' join from here
JOIN #TEMP1 TRB ON TRB.DealNo = TP.DealID
AND TRB.TradeNo = TP.TradeNum
AND TRB.Currency = TP.CurrencyCode
-- remove 'INNER HASH' join from here
JOIN TCH_t_ExchangeRate ER ON TRB.Currency = ER.CcyCode
AND ER.ValidFrom <= @.BusinessDayDate
AND ER.ValidTo >= @.BusinessDayDate
WHERE @.BusinessDayDate > TP.BusinessDayValidFrom
AND @.BusinessDayDate <= TP.BusinessDayValidTo
AND (TP.ChangeType = @.ChangeType OR @.ChangeType = 'All')Please post DDL and data that demonstrates the problem. If you have a
parallel plan, try adding the MAXDOP 1 optimizer hint to see if that
fixes the problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Rob Jones" <rob.jones@.barclays.co.uk> wrote in message
news:97dbfc5b.0308180057.2bbe0bea@.posting.google.com...
> Hi all,
> I've got 2 problems with the following statement, which leads me to
> believe there is a bug somewhere.
> This weekend we upgraded from SQL7 to SQL2000 (SP3) and it all went
> smoothly except for this statement below 'losing' a couple of records.
> It is contained in a stored procedure (believe me you don't want to
> see it) and gives me 2 problems.
> 1. It seems depending on the parameters passed which affect the volume
> of records being processed all the records either come through or not.
> Now I KNOW the record is in ALWAYS in #TEMP1 (don't question it is as
> I'm sure) as I debug it just before the insert. When I change the temp
> table to a permanent table and run the raw SQL through QA using this
> permanent table it always gets inserted and never loses the record,
> hence I know it's not the join leading to the record not coming
> through
> 2. Now the strange thing is if I add 'INNER HASH JOIN' to the 2 joins
> then the record is lost but 8 records do come through, but if I just
> make it a 'JOIN' then absolutely nothing comes through. Why is this?
> How come removing the 'INNER HASH' reduce the number of records?
> I know this sounds a bit of a ramble but I've spent several hours
> investigating this and still have no idea. What I was wondering is has
> anyone had any problems when upgrading with joins/temp tables/stored
> procs somehow not selecting all the records expected?
>
> SQL:
> INSERT INTO #TEMP2(Day, Year, DataSource, StartDate,
> MaturityDate, BusinessDayDate, DealID, TradeNum, Flowsign, Currency,
> CurrencyUnit, ReportingCurrency,InstrumentType, ChangeType, FXRate,
> SetNumber)
> SELECT DISTINCT
> @.StartDate,
> DATEPART(Year,@.BusinessDayDate),
> TRB.DataSource,
> @.StartDate,
> TP.MaturityDate,
> TRB.BusinessDate,
> TRB.DealNo,
> TRB.TradeNo,
> TRB.Flowsign,
> TP.CurrencyCode,
> TRB.CurrencyUnit,
> @.RepCurrency,
> TRB.Instrument,
> TP.ChangeType,
> ER.Rate,
> 1
> FROM TCH_gt_TradeProfile TP
> -- remove 'INNER HASH' join from here
> JOIN #TEMP1 TRB ON TRB.DealNo = TP.DealID
> AND TRB.TradeNo = TP.TradeNum
> AND TRB.Currency = TP.CurrencyCode
> -- remove 'INNER HASH' join from here
> JOIN TCH_t_ExchangeRate ER ON TRB.Currency = ER.CcyCode
> AND ER.ValidFrom <=> @.BusinessDayDate
> AND ER.ValidTo >=> @.BusinessDayDate
> WHERE @.BusinessDayDate > TP.BusinessDayValidFrom
> AND @.BusinessDayDate <= TP.BusinessDayValidTo
> AND (TP.ChangeType = @.ChangeType OR @.ChangeType => 'All')