Syntax error (missing operator)

Question

I am not a trained programmer, so trying to do this is difficult. I am getting the above error with this SQL code, and I cannot figure out the reason. Please advise.

SELECT a.agentname as 'Salesforce AgentName', a.loannumber, ag.agentname as 'portal agentname'
from salesforce a 
inner join portal b ON a.loannumber = b.loannumber

left join agents ag ON b.agentid = ag.agentid
where a.agentname <> ag.agentname

MS Access has a query design window which makes it easy to find problems with syntax. — Fionnuala
– Fionnuala, Commented Jan 20, 2012 at 22:03

Martin Smith · Accepted Answer · 2012-01-20 21:37:49Z

2

Bit of a guess (based on here)

SELECT a.agentname  AS 'Salesforce AgentName',
       a.loannumber,
       ag.agentname AS 'portal agentname'
FROM   ((salesforce as a )
       INNER JOIN portal as b
         ON a.loannumber = b.loannumber)
       LEFT JOIN agents ag
         ON b.agentid = ag.agentid
WHERE  a.agentname <> ag.agentname

BTW it is usually an error to refer to outer joined table columns in the WHERE clause unless you account for the possibility that they are NULL

answered Jan 20, 2012 at 21:37

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2012-01-20 22:31:31Z

Access' db engine has strict requirement for parentheses when your SELECT statement includes more than 1 join. I suggest you start with a simplified version of the query (discard the field list and the WHERE clause) so you can focus on getting the joins right.

SELECT *
FROM
    (salesforce AS a
    INNER JOIN portal AS b
    ON a.loannumber = b.loannumber)
    LEFT JOIN agents AS
    ag ON b.agentid = ag.agentid;

Assuming that version works, try this:

SELECT
    a.agentname AS [Salesforce AgentName],
    a.loannumber,
    ag.agentname AS [portal agentname]
FROM
    (salesforce AS a
    INNER JOIN portal AS b
    ON a.loannumber = b.loannumber)
    LEFT JOIN agents AS
    ag ON b.agentid = ag.agentid
WHERE a.agentname <> ag.agentname;

Notice I enclosed the aliases for the column names with square brackets instead of single quotes. The single quotes would not trigger an error, but they would be included in the column header ... which I doubt you want.

You would do yourself a favor by building Access queries in Access' query designer. It will make it difficult to create a query with syntax the db engine won't accept.

Edit: If the purpose of this query is to compare agentname values from the salesforce and agents tables, perhaps you may want to try this as the WHERE clause:

WHERE a.agentname <> ag.agentname OR ag.agentname Is Null;

Collectives™ on Stack Overflow

Syntax error (missing operator)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related