fulltextsqlquery exclude records with blank department

Question

The following FullTextSQLQuery returns results:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
ORDER BY HireDate DESC

The following FullTextSQLQuery does not return any results:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND Department != '' 
ORDER BY HireDate DESC

The only difference is the addition of "Department != ''" in the second query. I know there are records in which Department is blank. Why am I not getting any results for the second query?

UPDATE

When I query for text within the Department column, I don't get any results, even though there is text in the column:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND Department LIKE 'A%'
ORDER BY HireDate DESC
//no results!

If I do the same query on a different column, I get results:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND PreferredName LIKE 'A%'
ORDER BY HireDate DESC
//results!

Also if I do a People Search through the UI for Department:"name of department" (using an actual department name), I get results, so I know for sure that Department is being crawled. Does anyone have some insight on this?

Answer 1

Try using the following query:

SELECT PreferredName, Department, WorkEmail, HireDate
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND Department LIKE '_%'
ORDER BY HireDate DESC

Edit:

This actually looks like a problem with the Department field itself.

For one, "FullTextSQLQuery does not track whether a crawled property is null. Null values are not stored. Therefore if you OR two managed properties together and there is not a value for both, then the record will not be returned." FullTextSQLQuery tips

Also, I found a scraped forum post (that I can't link to, since it was scraped) that said that including the Department field (as well as OfficeNumber or JobTitle) always returned 0 results. This may be an issue with the property not being crawled.

You could try using MOSS Search Coder from CodePlex.

Good luck.