Subject Re: [IBO] Concatenate strings in result sets
Author Helen Borrie
At 10:49 PM 02-07-01 +0000, you wrote:
>Is there any way to work around getting a NULL for the result set if
>FirstName is NULL? The only thing I can think of is inserting a
>blank (' ') in the FirstName Field. Is there a more elegant solution?

Yes: one way is do it on the server. Write a selectable stored procedure that takes care of the NULL (or empty string, if you use a default) and returns the dataset you want (including the correctly calculated Full Name column).

The other way is to create a calculated field on the client. Define it in the CalculatedFields property of the dataset:

(from the help) The format of this property emulates a column definition in a CREATE TABLE statement where you declare the COLUMN NAME, COLUMN TYPE and its nullable status, if applicable. For example (paraphrasing here):

// making sure it's long enough to accommodate the full length)

Then, place code in the OnCalculateField event handler to construct the fullname, e.g
S: string;
S := '';
with ARow do
if length(ByName('FirstName')) > 0 then
S := ByName('FirstName');
if length(ByName('LastName')) > 0 then
if length(S) > 0 then
S := S + ' ';
S := S + ByName('LastName');
ByName('FullName') := S;

The OnCalculateField handler gets called automatically whenever the dataset is opened or refreshed but you can force a recalculation any time you want, by calling the CalculateFields() method of the row or the dataset.


All for Open and Open for All
InterBase Developer Initiative ยท