MSSQL Join - Incorrect Syntax near the keyword 'where'

Nico2011
Nico2011 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
FROM Villas) v WHERE  there'sa V in line8

Author

Commented:
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...
Top Expert 2011

Commented:
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')
Commented:
There was one select * to much.

And for the rest, better allignment, using table alias where possible, filter as early as possible and your code is more readable .... so errors easyer to find.

select Diary.*, x.* 
from 
(select row_number() over (order by v.distance) rn, v.* 
  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
        WHERE for_rent = 1 
        AND Problem = 0 
        AND price_low IS NOT NULL
    ) AS v 
  WHERE v.distance <= 10 
  ) AS x 
JOIN Diary on x.XID = Diary.PropID 
where Diary.StartDate not between '7-Jan-2012' and '28-Jul-2012'

Open in new window

Author

Commented:
Thanks very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial