I am using ES 2007.1.1210 and MyGeneration 1.2.0.7.

Let's say I have two tables, Employee and JobTitle.
Employee has columns PKEmployeeID, Name, and FKJobTitleID. FKJobTitleID is nullable.
JobTitle has columsn PKJobTitleID, Name.
There is a relationship between the two table via the JobTitleID fields.

I am binding a DetailsView at runtime to a query with the following code:

EmployeeCollection emps = new EmployeeCollection();
EmployeeQuery eq = new EmployeeQuery("eq");
JobTitleQuery jq = new JobTitleQuery("jq");

eq.Select(eq.PKEmployeeID, eq.Name, jq.Name.As("JobName"));
eq.LeftJoin(jq).On(eq.FKJobTitleID == jq.PKJobTitleID);
eq.Where(eq.PKEmployeeID == 42);
if (emps.Load(eq))
{
DetailsView1.DataSource = emps.LowLevelBind();
DetailsView1.DataBind();
}

This works if Employee.FKJobTitleID is not null but not when it is null.
The problem is that there is no property made for JobName when FKJobTitleID is null, though the SQL does return a null value for it.

I can't recall of the top of my head or not if Coalesce is supported in ES2007 but I know it is in ES2008. I think it is in ES2007 also. Then you could do this:

MyCollection coll = new MyCollection();
coll.Query.Select(coll.Query.LastName.Coalesce("'Smith'").As("MyColumn));
coll.Query.Load();

or in  your case ...

jq.Name.Coalesce('""').As("JobName"));

and it might do the trick?

Thank you, Coalesce works great and is in ES2007. I'll have to remember that this support exists, because it can be useful to return "<N/A>" etc. instead of null sometimes.

 For anyone trying to copy and paste the second statement Mike wrote, it's actually:

jq.Name.Coalesce("''").As("JobName");

 Just a switch of the double quotes and single quotes was all.

Thanks again!