Syntax Help!

[jared.pohl]

Hi guys..

When I try and run the following code it says that there is a missing
operator..
SELECT
curr.portfolio_code,
curr.date,
(curr.gross_index-mo1.gross_index)/mo1.gross_index AS 1MONTH_RETURN,
(curr.gross_index-mo1.gross_index)/mo1.gross_index AS 3MONTH_RETURN
FROM portfolio_performance AS curr
LEFT OUTER JOIN portfolio_performance AS mo1
ON curr.date =
dateserial(year(dateadd('d',-1,dateserial(Year([mo1].date),
MONTH([mo1].date)+2, 1))),
month(dateadd('d',-1,dateserial(Year([mo1].date), MONTH([mo1].date)+2,
1))),day(dateadd('d',-1,dateserial(Year([mo1].date),MONTH([mo1].date)+2,

1))))
LEFT OUTER JOIN portfolio_performance AS mo3
ON curr.date =
dateserial(year(dateadd('d',-1,dateserial(Year([mo3].date),
MONTH([mo3].date)+4, 1))),
month(dateadd('d',-1,dateserial(Year([mo3].date), MONTH([mo3].date)+4,
1))),day(dateadd('d',-1,dateserial(Year([mo3].date),MONTH([mo3].date)+4,

1))))
;
However, when I run the same code, minus the second left outer join it
works perfectly..
SELECT
curr.portfolio_code,
curr.date,
(curr.gross_index-mo1.gross_index)/mo1.gross_index AS 1MONTH_RETURN
FROM portfolio_performance AS curr
LEFT OUTER JOIN portfolio_performance AS mo1
ON curr.date =
dateserial(year(dateadd('d',-1,dateserial(Year([mo1].date),
MONTH([mo1].date)+2, 1))),
month(dateadd('d',-1,dateserial(Year([mo1].date), MONTH([mo1].date)+2,
1))),day(dateadd('d',-1,dateserial(Year([mo1].date),MONTH([mo1].date)+2,

1))));
Any help would be appreciated.. im sure its only something minor!

[Razvan Socol]

What flavour of SQL are you using ? This is a Microsoft SQL Server
group, but your code looks like a Microsoft Access query.

Razvan

[jared.pohl]

Razvan,

Well its written as you would ANSI SQL but the dateadd function is
access... noone in the access newsgroup will help at all so i just
asked here to see if anyone here coul d help.

[Erland Sommarskog]

ja********@gmail.com (ja********@gmail.com) writes:

Well its written as you would ANSI SQL but the dateadd function is
access... noone in the access newsgroup will help at all so i just
asked here to see if anyone here coul d help.

Just because it's ANSI SQL does not mean that it will compile in any
DB engine. If the Access people does not know their syntax, how could
we know it.

First, try the query only with the FROM clause and the second LEFT JOIN,
to see if that works. If it does, you can rule out that you have an
error with the many parentheses.

Next try this syntax:

SELECT
FROM
LEFT OUTER JOIN
LEFT OUTER JOIN
ON
ON

I have an impression that Access prefers this order, unreadable as it may
be.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx