Items not included SharePoint search results if a property missing

Question

I use an MSSQLFT query to poll Sharepoint's search through Search.asxm web service.

The query goes like this:

SELECT PreferredName, Department, AccountName, MobilePhone, WorkPhone
       Title, JobTitle, OfficeNumber
FROM SCOPE()
WHERE 
     "scope"="People" AND JobTitle="Something"
ORDER BY
       Department, OfficeNumber

Some people do not have Depmartment attribute set and hence are not included in search results.
If I remove the "Department" part from "Order by" clause, everything works fine. Is there a way to provide "default value" for such missing property so that these people would still get included?

Kelly S. French Kelly S. French · Accepted Answer · 2011-09-27T18:51:39

Have you tried including the Department field in the where. You might have to hack it to say something like AND (dept = '' or dept not null). In other words, any value is acceptible. That would get the rows included in the results so the Order By clause could operate on them.

Items not included SharePoint search results if a property missing

1 Answers