What is Select 'X'?

 sSQL.Append(" SELECT 'X' ");
        sSQL.Append(" FROM ProfileInsurancePlanYear ");
        sSQL.Append(" WHERE ProfileID = " + profileid.ToString() + " AND CropYear = " + cropyear.ToString());

This was a query that was originally hitting an access back end. I have moved it over to SQLCE and am perplexed about what this query is supposed to do.

The table structure it hits is:

ProfileID
InsurancePlanID
CropYear
INsurance_Price
Levels_XML

I am assum开发者_开发百科ing this would select something from the Levels_XML column where the profileid and cropyear match?

Does this even work in sqlCE?

---

This type of query is typically used to see if a row exists. If a row is found, the query will return a single character, X. Otherwise, it will be an empty result set... You could also say

 sSQL.Append(" SELECT count(*) ");
 sSQL.Append(" FROM ProfileInsurancePlanYear ");
 sSQL.Append(" WHERE ProfileID = " + profileid.ToString() + 
             " AND CropYear = " + cropyear.ToString());

Which will return a result with either 0 or some positive number. Different approaches both asking the database simply to indicate whether or not any records existing matching the condition.

---

In general, Select 'X' is used with the EXISTS, as the EXISTS predicate does not care about the values in the rows but just if those rows exist.

For example:-

Q.Find employees who have at least one person reporting to them.

SELECT last_name, employee_id FROM employee outer WHERE 
EXISTS (SELECT 'X' FROM employee manager_id=outer.employee_id)