Left outer join...

[Gazzamuffin]

Can you set a default value for the returned fields when using the left outer join? As it stands it will return a blank field, but I need a default value without having to change the table.

 

[slim2]

Is this what you are looking for. 0 will be appended to the output table. I hope it helps.


SELECT a.*, b.field, "0" AS newone
FROM Tbale1 AS a LEFT JOIN Table2 AS b ON a.field = b.field;

 

[MalcolmW]

As you cannot edit the SELECT portion of the SQL statement in Crystal, the above wouldn't work. As well, the value for b.field would still be null, as appending another value to the result set wouldn't alter the value of b.field.

SELECT a.somefield, b.field
FROM Table1 AS a LEFT JOIN Table2 AS b ON a.field = b.field

Instead, create a formula in Crystal check for a null value, and replace the null value with your default.
//Assumes {b.field} is a string
If IsNull({b.field}) then 'default value
Else {b.field}

There are other ways to solve this, including the use of SQL Expressions, views, stored procs, but this formula based solution will work with any database driver. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[Ngolem]

the simplest way is to convert nulls to a default value as a report option...but I would much better like to control this in a formula as Malcolm has shown