Syntax Help!

[jared.pohl@gmail.com]

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_ind ex-mo1.gross_index )/mo1.gross_index AS 1MONTH_RETURN,
(curr.gross_ind ex-mo1.gross_index )/mo1.gross_index AS 3MONTH_RETURN
FROM portfolio_perfo rmance AS curr
LEFT OUTER JOIN portfolio_perfo rmance AS mo1
ON curr.date =
dateserial(year (dateadd('d',-1,dateserial(Ye ar([mo1].date),
MONTH([mo1].date)+2, 1))),
month(dateadd(' d',-1,dateserial(Ye ar([mo1].date), MONTH([mo1].date)+2,
1))),day(datead d('d',-1,dateserial(Ye ar([mo1].date),MONTH([mo1].date)+2,

1))))
LEFT OUTER JOIN portfolio_perfo rmance AS mo3
ON curr.date =
dateserial(year (dateadd('d',-1,dateserial(Ye ar([mo3].date),
MONTH([mo3].date)+4, 1))),
month(dateadd(' d',-1,dateserial(Ye ar([mo3].date), MONTH([mo3].date)+4,
1))),day(datead d('d',-1,dateserial(Ye ar([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_ind ex-mo1.gross_index )/mo1.gross_index AS 1MONTH_RETURN
FROM portfolio_perfo rmance AS curr
LEFT OUTER JOIN portfolio_perfo rmance AS mo1
ON curr.date =
dateserial(year (dateadd('d',-1,dateserial(Ye ar([mo1].date),
MONTH([mo1].date)+2, 1))),
month(dateadd(' d',-1,dateserial(Ye ar([mo1].date), MONTH([mo1].date)+2,
1))),day(datead d('d',-1,dateserial(Ye ar([mo1].date),MONTH([mo1].date)+2,

1))));


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

[Razvan Socol]

Re: Syntax Help!

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@gmail.com]

Re: Syntax Help!

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]

Re: Syntax Help!

jared.pohl@gmai l.com (jared.pohl@gma il.com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx