Address fields can only be filtered using Distance expressions

Question

I'm trying to do an execute anonymous query for Contracts with a null/blank address. I get "Address fields can only be filtered using Distance expressions" in the query:

List<Contract> noAddress = [SELECT ID FROM Contract WHERE BillingAddress =: null];
System.Debug(noAddress.size());

I thought to try BillingAddress.city instead -- since I know you don't update the Address directly, but as such... and I got:

Line: 7, Column: 28 Didn't understand relationship 'BillingAddress' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

How can I make this query?

Answer 1

BillingAddress is a complex field. If you want to find records that have no populated values, it would look like this:

SELECT Id 
FROM Contract 
WHERE BillingStreet = NULL AND
      BillingCity = NULL AND
      BillingStateCode = NULL AND
      BillingPostalCode = NULL AND
      BillingCountryCode = NULL

(Note: This query presumes that you're using State and Country picklists. If not, use BillingState and BillingCountry instead.)

If you have a lot of records in your database, this may fail in Execute Anonymous, because it's a full-table scan. In that case, you could use the QUERY tab instead.

Also, if you're only interested in the number of records, you can instead use COUNT:

SELECT COUNT()
FROM Contract
WHERE ...