Invalid object name 'pos'. 1

[xavstone]

Hi all,

Any ideas why 'pos' is an invalid object name?

Thanks in advance,

Ben

ALTER PROCEDURE dbo.PortfolioReport

@portfolioId int

AS

SELECT

AVG(DATEDIFF(day, openDate, closeDate)) as 'Average Length',
MAX(DATEDIFF(day, openDate, closeDate)) as 'Longest',
MIN(DATEDIFF(day, openDate, closeDate)) as 'Shortest',

(SELECT SUM(closePrice - openPrice) FROM pos WHERE IsLong = 'TRUE' AND closeDate > '1950-01-01')-
(SELECT SUM(openPrice - closePrice) FROM pos WHERE IsLong = 'FALSE'AND closeDate > '1950-01-01') as 'Total Profit',



(SELECT SUM(

CASE 
WHEN closeDate > '1950-01-01' THEN closePrice
WHEN closeDate < '1950-01-01' THEN

(SELECT dbo.symbolData.priceClose
FROM dbo.dates
INNER JOIN dbo.symbolData ON dbo.dates.id = dbo.symbolData.dateId
INNER JOIN p ON dbo.symbolData.symbolId = pos.symbolId
WHERE dbo.dates.priceDate =

(SELECT MAX(priceDate) FROM dates as d
INNER JOIN d ON dbo.symbolData.dateId = d.id
WHERE dbo.symbolData.symbolId = pos.symbolId))

END

- openPrice) FROM pos WHERE IsLong = 'TRUE' AND closeDate > '1950-01-01') as 'Total Long Profit',
(SELECT MAX(closePrice - openPrice) FROM pos WHERE IsLong = 'TRUE' AND closeDate > '1950-01-01') as 'Biggest Long Gain',
(SELECT MIN(closePrice - openPrice) FROM pos WHERE IsLong = 'TRUE' AND closeDate > '1950-01-01') as 'Biggest Long Loss',

(SELECT SUM(openPrice - closePrice) FROM pos WHERE IsLong = 'FALSE'AND closeDate > '1950-01-01') as 'Total Short  Profit',
(SELECT MAX(openPrice - closePrice) FROM pos WHERE IsLong = 'FALSE'AND closeDate > '1950-01-01') as 'Biggest Short Gain',
(SELECT MIN(openPrice - closePrice) FROM pos WHERE IsLong = 'FALSE'AND closeDate > '1950-01-01') as 'Biggest Short Loss'

FROM dbo.position AS pos

INNER JOIN posPortMatrix ON pos.id = dbo.posPortMatrix.positionId

INNER JOIN portfolio ON posPortMatrix.portfolioId = portfolio.id

WHERE portfolio.id = @portfolioId

RETURN

 

[bborissov]

Because POS is alias of the dbo.position and you can't use it in subqueries. You should use the full name of the table.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[xavstone]

ok, thanks