sql syntax error in embeded select clause

[bobmac-]

Hi,

When I place the following code into a query I get a syntax error (syntax error missing operator in mgrwms_party.customer_no WHERE mgrwms_party.customer_no IN (SELECT T2.customer_no FROM mgrwms_party AS T2 GROUP BY T2.customer_no HAVING COUNT(T2.customer_no)=1)

code:
SELECT Left([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")-1) AS ServicePoint, Mid([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")+1) AS ServiceNo, mgrwms_object_authority.party_no AS PartyNo, mgrwms_party.customer_no AS CustomerNo, mgrwms_party.given_name AS GivenName, mgrwms_party.surname AS Surname, mgrwms_party.address_1, mgrwms_party.address_2, mgrwms_party.address_3, mgrwms_party.post_code, mgrwms_party.phone_no
FROM (mgrwms_node INNER JOIN (((mgrwms_object INNER JOIN mgrwms_object_authority ON mgrwms_object.object_no = mgrwms_object_authority.object_no) INNER JOIN mgrwms_object_type ON mgrwms_object.object_type = mgrwms_object_type.object_type) INNER JOIN mgrwms_party ON mgrwms_object_authority.party_no = mgrwms_party.party_no) ON mgrwms_node.node_object_no = mgrwms_object.object_no) INNER JOIN mgrwms_link_object ON mgrwms_node.link_object_no = mgrwms_link_object.object_no
GROUP BY mgrwms_object_authority.party_no, mgrwms_party.customer_no, mgrwms_party.given_name, mgrwms_party.surname, mgrwms_party.address_1, mgrwms_party.address_2, mgrwms_party.address_3, mgrwms_party.post_code, mgrwms_party.phone_no, mgrwms_object.object_name, mgrwms_link_object.link_object_no, mgrwms_object.object_type
HAVING (((mgrwms_link_object.link_object_no)=102978 Or (mgrwms_link_object.link_object_no)=102979 Or (mgrwms_link_object.link_object_no)=102980) AND ((mgrwms_object.object_type)=17))
ORDER BY Left([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")-1), Mid([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")+1), mgrwms_party.customer_no WHERE mgrwms_party.customer_no IN (SELECT T2.customer_no FROM mgrwms_party AS T2 GROUP BY T2.customer_no HAVING COUNT(T2.customer_no)=1);

The code works without the WHERE clause but I'm trying to get distinct customer.no
Can anyone spot the problem?

cheers
bob

 

[georgedwilkinson]

You put the where clause in the middle of the order by clause. That'll never work.

The where clause goes after the From clause is done. Something like this:

SELECT Left([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")-1) AS ServicePoint, Mid([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")+1) AS ServiceNo, mgrwms_object_authority.party_no AS PartyNo, mgrwms_party.customer_no AS CustomerNo, mgrwms_party.given_name AS GivenName, mgrwms_party.surname AS Surname, mgrwms_party.address_1, mgrwms_party.address_2, mgrwms_party.address_3, mgrwms_party.post_code, mgrwms_party.phone_no
FROM (mgrwms_node INNER JOIN (((mgrwms_object INNER JOIN mgrwms_object_authority ON mgrwms_object.object_no = mgrwms_object_authority.object_no) INNER JOIN mgrwms_object_type ON mgrwms_object.object_type = mgrwms_object_type.object_type) INNER JOIN mgrwms_party ON mgrwms_object_authority.party_no = mgrwms_party.party_no) ON mgrwms_node.node_object_no = mgrwms_object.object_no) INNER JOIN mgrwms_link_object ON mgrwms_node.link_object_no = mgrwms_link_object.object_no
[COLOR=red]WHERE mgrwms_party.customer_no IN (SELECT T2.customer_no FROM mgrwms_party AS T2 GROUP BY T2.customer_no HAVING COUNT(T2.customer_no)=1)[/COLOR]
GROUP BY mgrwms_object_authority.party_no, mgrwms_party.customer_no, mgrwms_party.given_name, mgrwms_party.surname, mgrwms_party.address_1, mgrwms_party.address_2, mgrwms_party.address_3, mgrwms_party.post_code, mgrwms_party.phone_no, mgrwms_object.object_name, mgrwms_link_object.link_object_no, mgrwms_object.object_type
HAVING (((mgrwms_link_object.link_object_no)=102978 Or (mgrwms_link_object.link_object_no)=102979 Or (mgrwms_link_object.link_object_no)=102980) AND ((mgrwms_object.object_type)=17))
ORDER BY Left([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")-1), Mid([mgrwms_object].[object_name],InStr([mgrwms_object].[object_name],"-")+1), mgrwms_party.customer_no;

This is air code...I haven't tested it and don't know if it will work. I posted it to demonstrate the principle only.

 

[bobmac-]

Thanks
I tried putting the where clause where you suggested, it got rid of the error but I but I'm still getting duplicate customer_no rows. I took the where clause from an example on the Net which is supposed to show distinct on one field only, but I'm not sure how setting the count equal to one is supposed to work

cheers

bob

 

[georgedwilkinson]

Oh, you didn't mention that.

The reason the field "mgrwms_party.customer_no" would be duplicated is because one of the other fields in the "group by" clause has more than one unique value as related to that field.

When you look at the output from the query, you should see it. If there are 2 rows that have the same customer_no but totally different anything elses, there's your problem.

 

[bobmac-]

I thankyou
You are correct. I'll see if I can un-group something
cheers
bob

 

[georgedwilkinson]

NP. Always glad to help out.