Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
how to use calculated fields in ElevateDB? |
Wed, Aug 7 2019 7:38 AM | Permanent Link |
Polywick Studio | can you give an example of a calculated field? what do I put for default expression = ? |
Wed, Aug 7 2019 10:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Aug 10 2019 1:53 PM | Permanent Link |
Polywick Studio | 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. |
Sun, Aug 11 2019 3:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Aug 14 2019 10:47 AM | Permanent Link |
Adam Brett Orixa Systems | 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", '') ) |
Thu, Aug 15 2019 4:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Very good point, if only because you can't use user defined functions in computed columns Roy Lambert |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |