Link to home
Start Free TrialLog in
Avatar of Nico2011
Nico2011Flag for United Kingdom of Great Britain and Northern Ireland

asked on

MSSQL Join - Incorrect Syntax near the keyword 'where'

Hello Experts,

I have an issue with the foillowing MSSQL Query, which returns the error below - it is a rather complex query (in my opinion!), but I need to link it to a booking calendar so we only get properties with availability showing...:

select * from (select row_number() over (order by distance) rn, * from (select * from (SELECT ID as XID, PayMonthly, ccaccepted, pool, sattv, disabled_access, internet, restaurants, shops, beach, golf, skiing, tennis, hiking, for_rent, for_sale, price_low, rental_currency, rental_USD, rental_EUR, rental_GBP, Problem, Property_Name, Town, Region, Property_Type, Property_Description, short_description, bedrooms, sleeps, photo, photo_two, photo_three, thumb, lat, lng, (3960 * acos( cos( radians(43.64701) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(7.00241) ) + sin( radians(43.64701) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas) v WHERE for_rent = 1 AND Problem = 0 AND distance <= 10 AND price_low IS NOT NULL) q) x JOIN Diary on XID = Diary.PropID where NOT(StartDate between '7-Jan-2012' and '28-Jul-2012')
 
Microsoft OLE DB Provider for SQL Server

 error '80040e14'

Incorrect syntax near the keyword 'where'.

/villa-results-list.asp, line 112

THANKS in advance.
Avatar of MFlaig
MFlaig
Flag of Germany image

FROM Villas) v WHERE  there'sa V in line8
Avatar of Nico2011

ASKER

Thanks, but that v is required.  If you take away everything after and including the word JOIN, the query qorks, but it's when I join the tables that it falls over...
Your problem appears to be the last WHERE.

where NOT(StartDate between '7-Jan-2012' and '28-Jul-2012')

it is wrong.

It should be where ( StartDate NOT between '7-Jan-2012' and '28-Jul-2012')
ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very much.