By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

Syntax Help!

P: n/a
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!

May 2 '06 #1
Share this Question
Share on Google+
3 Replies


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

Razvan

May 2 '06 #2

P: n/a
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.

May 2 '06 #3

P: n/a
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
May 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.