CODEWITHSUNDEEP
sql
lisa
lisa

Reputation: 91

Newbie - incorrect syntax error near the word where

SELECT
   o.id, o.Name, o.StageName, o.[Type], o.Account__c, o.SalesPerson__c,
   o2.AccountId__c,o2.Sales_Person__c, o2.InvoiceDate__c
FROM customers.dbo.sales as o 
INNER JOIN customers.dbo.account as a on a.Id = o.AccountId 
INNER JOIN
( 
    Select MAX(Id)Id, AccountId 
    FROM customers.dbo.order__c
    WHERE Sales_Person__c <> NULL
) as o1 
INNER JOIN Customers.dbo.Order__c as o2 on o2.Id = o1.Id
WHERE (o.SalesPerson__c = NULL) 
AND (o.[Type] = 'Renewal') 
AND (o.StageName NOT LIKE 'Closed%')

Not sure what I did wrong. I'm getting the following error: Incorrect syntax near the keyword 'Where'

I'm trying to get the most recent Order for each account. Any help would be greatly appreciated.

Upvotes: 1

Views: 938

Answers (2)

The Jonas Persson
The Jonas Persson

Reputation: 1746

Your Inner Join called o1 doesn't join on anything. I'm not sure exactly what you are trying to do, but I think if you remove the inner join for o1, and instead have that as an exists in your where clause, it might work better. You also have to change your o2 join to join on o2.AccountId = a.Id, which I think is what you are trying to do.

select o.id
, o.Name
, o.StageName
, o.[Type]
, o.Account__c
, o.SalesPerson__c
, o2.AccountId__c
, o2.Sales_Person__c
, o2.InvoiceDate__c
from customers.dbo.sales as o 
Inner Join customers.dbo.account as a 
    on a.Id = o.AccountId 
INNER JOIN Customers.dbo.Order__c as o2 
    on o2.AccountId = a.Id
Where (o.SalesPerson__c is NULL) 
and (o.[Type] = 'Renewal') 
and (o.StageName NOT LIKE 'Closed%')
and exists(
    Select MAX(Id) as id 
    from customers.dbo.order__c c
    where c.Sales_Person__c is not NULL
    and id = o2.id)

Upvotes: 2

Taryn
Taryn

Reputation: 247670

Your issue is the <> NULL and the = NULL, you need to use IS NULL or IS NOT NULL. Depending on your RDBMS you also need a GROUP BY in your subquery with the MAX():

select o.id
    , o.Name
    , o.StageName
    , o.[Type]
    , o.Account__c
    ,o.SalesPerson__c
    , o2.AccountId__c
    , o2.Sales_Person__c
    , o2.InvoiceDate__c
from customers.dbo.sales as o 
Inner Join customers.dbo.account as a 
    on a.Id = o.AccountId 
INNER JOIN
(
    Select MAX(Id)Id, AccountId 
    from customers.dbo.order__c
    where Sales_Person__c IS NOT NULL
    GROUP BY AccountId -- depending on the RDBMS you need a GROUP BY
) o1 
INNER JOIN Customers.dbo.Order__c  o2 
    on o2.Id = o1.Id
Where (o.SalesPerson__c IS NULL) 
    and (o.[Type] = 'Renewal') 
    and (o.StageName NOT LIKE 'Closed%')

Upvotes: 2

Related Questions