can you give an example of a calculated field?

what do I put for default expression = ?

Polywick


>can you give an example of a calculated field?
>
>what do I put for default expression = ?

Assuming that its a varchar column you'd just put '?' (note the quote marks - they need to be there) in the computed expression box on the computed tab.

Example

CREATE TABLE "Coding"
(
"_Code" VARCHAR(10) COLLATE "ANSI_CI" NOT NULL,
"_Sequence" INTEGER,
"_Owner" VARCHAR(10) COLLATE "ANSI_CI",
"_Description" VARCHAR(50) COLLATE "ANSI_CI" NOT NULL,
"_InOut" VARCHAR(1) COLLATE "ANSI_CI" DEFAULT '?' NOT NULL,
"_DateClosed" DATE,
"_xSubAccount" BOOLEAN COMPUTED ALWAYS AS _Owner IS NOT NULL,
CONSTRAINT "PK" PRIMARY KEY ("_Code")
)


Roy

CREATE TABLE "employees"
(
"id" VARCHAR(38) COLLATE "UNI" NOT NULL,
"first_name" VARCHAR(50) COLLATE "UNI",
"middle_initial" VARCHAR(50) COLLATE "UNI",
"last_name" VARCHAR(50) COLLATE "UNI",
"suffix" VARCHAR(20) COLLATE "UNI",
)

How would I create a computed field "name" which is:
if middle <> ""
 name = first_name + " " + middle_initial + " " + last_name
else
 name = first_name + " " + last_name
if suffix <> ""
 name = name + " " + suffix

Does the above make sense?
This would make a name, which comprises of name + middle_initial + lastname + suffix.

If middle name is blank, it wouldn't be shown.
If suffix is blank, it wouldn't be shown.

Polywick


I had the same problem and wrote a function to handle it - my Reverse Catenate Fields function

CREATE FUNCTION "RCF" (IN "In1" VARCHAR COLLATE "ANSI", IN "In2" VARCHAR COLLATE "ANSI", IN "Separator" VARCHAR COLLATE "ANSI")
RETURNS VARCHAR(1024) COLLATE "ANSI"
BEGIN
DECLARE Output VARCHAR;

SET Output = COALESCE(In2,'');

IF (In1 IS NOT NULL) AND (In1 <> '') THEN
IF (In2 IS NOT NULL) AND (In2 <> '') THEN
 SET Output = Output + Separator + In1;
 ELSE SET Output = In1;
END IF;
END IF;

RETURN Output;

END
DESCRIPTION 'Reverse combine fields'
VERSION 1.00

You need to "stack" it if there are several fields involved eg

RCF(Surname,RCF(MiddleName,Forename,' '),' ')

Then a computed field would simply have the above in its definition

Functions are at database level so you'll need to create the above function in all databases you want to use it in

Roy Lambert

I haven't tested this. But in this case I think you can use COALESCE to your advantage to include / exclude blank fields.

CREATE TABLE "employees"
(
"id" VARCHAR(38) COLLATE "UNI" NOT NULL,
"first_name" VARCHAR(50) COLLATE "UNI",
"middle_initial" VARCHAR(50) COLLATE "UNI",
"last_name" VARCHAR(50) COLLATE "UNI",
"suffix" VARCHAR(20) COLLATE "UNI",
"name" VARCHAR(170) COMPUTED ALWAYS AS
 COALESCE("first_name", '')
 + COALESCE(' ' + "middle_initial" + ' ', '')
 + COALESCE("last_name", '')
 + COALESCE("suffix", '')
)

Adam


Very good point, if only because you can't use user defined functions  in computed columns


Roy Lambert