2

I'm currently trying to run the following SOQL query using the REST API:

SELECT Id, accountId, COUNT_DISTINCT(accountId) 
FROM Contact 
WHERE Phone LIKE '%" + CLI + "' 
  OR HomePhone LIKE '%" + CLI + "' 
  OR MobilePhone LIKE '%" + CLI + "'"

However I get 400 bad request returned every time. If I take COUNT_DISTINCT out I get the following result:

{
  "totalSize": 2,
  "done": true,
  "records": [
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v27.0/sobjects/Contact/003M000000LJ2233P"
      },
      "Id": "003M000000LJ2233P",
      "AccountId": "001M000000Op99999",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v27.0/sobjects/Account/001M000000Op99999"
        }
      }
    },
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v27.0/sobjects/Contact/001M000000O123456F"
      },
      "Id": "001M000000O123456F",
      "AccountId": "001M000000OpMj5555",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v27.0/sobjects/Account/001M000000OpMj5555"
        }
      }
    }
  ]
}

The end goal is to get a count of the number of the accounts for all contacts returned (i.e 15 contacts found from 3 different accounts).

Is that possible using COUNT_DISTINCT or will I have to use C# to process the result?

3

I think you're missing your GROUP BY in the SOQL query. Try debugging it in workbench.

Your SOQL should be

SELECT Id, COUNT_DISTINCT(accountId) FROM Contact WHERE Phone LIKE '%" + CLI + "' OR HomePhone LIKE '%" + CLI + "' OR MobilePhone LIKE '%" + CLI + "'" GROUP BY Id
  • Using that exact query I get a '400 Bad Response' back. Could it be potentially something underlying with the CLIs or is there something in the query that could cause it? – lethalMango May 15 '13 at 15:46
  • What is CLI? Also, you'll either need to build up the SOQL as a string or use variable binding (salesforce.com/us/developer/docs/apexcode/Content/…). – Mike Chale May 15 '13 at 16:35
  • 1
    What are you actually sending on the wire? Are you URL encoding the SOQL query? I just tried $ curl -L -H 'X-PrettyPrint: 1' -H 'Authorization: Bearer 00D...' 'https://devorg-com-dev-ed.my.salesforce.com/services/data/v27.0/query?q=SELECT%20Id%2C%20COUNT_DISTINCT(accountId)%20FROM%20Contact%20WHERE%20Phone%20LIKE%20%27%25123%27%20OR%20HomePhone%20LIKE%20%27%25123%27%20OR%20MobilePhone%20LIKE%20%27%25123%27%20GROUP%20BY%20Id' and it worked just fine. – metadaddy May 15 '13 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.