SQL runs differently on SQL 7.0 and SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL runs differently on SQL 7.0 and SQL 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-05 : 08:28:53

Michael Rathbun writes "Hi,
I'm running an inherited piece of code (more than ugly) on SQL server 7.0 as a stored procedure. It returns a cartesian production on SQL Server 2000. It appears to be failing just at the last join on officerid before the last WHERE statement.

Any ideas?

The query should return only investments that are tied to an investment officer. Instead on 2000 it shows all investments possible for each investment officer.

Again I did not write this horrific SQL...

Does anyone have a parsing tool for SQL ? I could sure use one since I've inherited this code...

SELECT
Buckets.PortfolioID,
InvestmentOfficer.FirstLastName,
CASE WHEN IsNull([Invest].[CommonName], '') = '' THEN [Holdings].[Cusip] ELSE [Invest].[CommonName] END AS CommonName,
Round(Sum([AccruedIncome]+[MarketValue]),2) AS CurrValue,
Invest.Leverage,
[TotalCommitment]-[DrawnToDate]- ISNULL([ProFormaDraws],0)+[TotalValue] AS PortValue,
USys_rsubDiversification.TotalValue
FROM
InvestmentOfficer
INNER JOIN
(((Buckets
INNER JOIN
Holdings ON Buckets.BucketNum = Holdings.BucketNum)
LEFT JOIN
Invest ON Holdings.Cusip = Invest.CUSIP)
INNER JOIN
(
--USYS_rsubdiversification
SELECT
USys_TotalPrivate.AsOfDate,
ISNULL(USys_TotalPrivate.TotalPrivate, 0) AS TotalPrivate,
ISNULL(USys_TotalPublic.TotalPublic, 0) AS TotalPublic,
Sum(ISNULL(USys_TotalPrivate.TotalPrivate, 0) + ISNULL(USys_TotalPublic.TotalPublic, 0)) AS TotalValue,
Portfolio.PortfolioID,
Portfolio.PortfolioName,
ISNULL(Portfolio.DefaultAllocationPct, 0) AS DefaultAllocationPct,
Portfolio.Desc_,
ISNULL(Portfolio.TotalCommitment, 0) AS TotalCommitment,
ISNULL(Portfolio.DrawnToDate, 0) AS DrawnToDate,
ISNULL(LimitsGuideline.Public_, 0) AS Public_,
ISNULL(LimitsGuideline.Private, 0) AS Private,
ISNULL(([USys_TotalFund].[TotalFund]), 0) AS TotalFund,
ISNULL(Portfolio.ProFormaDraws, 0) AS ProFormaDraws,
ISNULL(Portfolio.CapitalReturned, 0) AS CapitalReturned
FROM (((Portfolio LEFT JOIN
(
--usys_totalprivate
SELECT
Buckets.PortfolioID, MAX( Holdings.AsOfDate ) AS asofdate, Sum([MarketValue]+[AccruedIncome]) AS TotalPrivate
FROM
InvestmentOfficer
INNER JOIN
(Holdings
INNER JOIN
Buckets ON Holdings.BucketNum = Buckets.BucketNum) ON InvestmentOfficer.OfficerID = Buckets.OfficerID
WHERE
(((InvestmentOfficer.IsPrivate) <> 0))
GROUP BY Buckets.PortfolioID --, Holdings.AsOfDate
HAVING (((Sum([MarketValue]+[AccruedIncome]))<>0))
)
USys_TotalPrivate ON Portfolio.PortfolioID = USys_TotalPrivate.PortfolioID)
LEFT JOIN
(
--usys_totalpublic
SELECT
Buckets.PortfolioID, MAX(Holdings.AsOfDate) AS asofdate, IsNull(Sum([MarketValue]+[AccruedIncome]),0) AS TotalPublic
FROM
InvestmentOfficer
INNER JOIN
(Holdings
INNER JOIN Buckets
ON Holdings.BucketNum = Buckets.BucketNum) ON InvestmentOfficer.OfficerID = Buckets.OfficerID
WHERE
(
((InvestmentOfficer.IsPrivate) = 0)
AND
((InvestmentOfficer.[is_fund]) = 0))
GROUP BY
Buckets.PortfolioID
)
USys_TotalPublic ON Portfolio.PortfolioID = USys_TotalPublic.PortfolioID
)
LEFT JOIN
(
--usys_totalfund
SELECT
Buckets.PortfolioID,
MAX(Holdings.AsOfDate) AS asofdate,
Sum([MarketValue]+[AccruedIncome]) AS TotalFund
FROM
InvestmentOfficer
INNER JOIN
(Holdings INNER JOIN Buckets ON Holdings.BucketNum = Buckets.BucketNum)
ON
InvestmentOfficer.OfficerID = Buckets.OfficerID
WHERE (
((InvestmentOfficer.[is_fund]) <> 0))
GROUP BY Buckets.PortfolioID
)
USys_TotalFund ON Portfolio.PortfolioID = USys_TotalFund.PortfolioID
)
INNER JOIN
LimitsGuideline
ON Portfolio.PortfolioID = LimitsGuideline.PortfolioID
WHERE
(
((Portfolio.StartDate) Is Not Null))
GROUP BY
USys_TotalPrivate.AsOfDate,
USys_Tot

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-05 : 08:33:40

Unfortunately ASK SQL Team cuts off at 4,000 characters, you'll need to repost the lot.

One suggestion I can make right away: do not enclose JOIN clauses in parentheses. They're not necessary in SQL Server (the guy/gal who wrote this came up from MS Access, right?)

Try taking out some parentheses and see what happens.

Honestly, whenever I get stuck with something like this, I start over. It's usually better because it forces me to understand what is supposed to happen, and I can build each piece one at a time, and is almost always MUCH faster than working with the original. I can tell you I'll NEVER be able to figure out what this code is supposed to do. I don't see much help for you if you need to maintain or modify this code; ditch it and write your own.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources