Tim

ElevateDB Error #1011 An error occured with the value Brown (The value was truncated)

select _Title,_Forename,_Surname, _sTitle+' '+_sForename+' '+_sSurname from contacts where _forename is null

_Title etc are VARCHARs
_sTitle etc are computed from IF(field is null,'',field)

Roy Lambert

Tim

This works

select _Title,_Forename,_Surname,
IF(_Title IS NULL,'',_Title)+' '+IF(_Forename IS NULL,'',_Forename)+' '+IF(_Surname IS NULL,'',_Surname)
from contacts where _forename is null

So it looks like its the computed field handling.

Roy Lambert

Roy,

<< ElevateDB Error #1011 An error occured with the value Brown (The value
was truncated) >>

Are you using 1.x or 2.x ?

<< select _Title,_Forename,_Surname, _sTitle+' '+_sForename+' '+_sSurname
from contacts where _forename is null >>

Is this a sensitive or insensitive result set ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Are you using 1.x or 2.x ?

Always the latest and greatest.

><< select _Title,_Forename,_Surname, _sTitle+' '+_sForename+' '+_sSurname
>from contacts where _forename is null >>
>
>Is this a sensitive or insensitive result set ?

I didn't ask, but just checking EDBManager is defaulting to sensitive currently.

Roy Lambert

Roy,

<< Always the latest and greatest. >>

The error message isn't correct for 2.00 B2:

http://www.elevatesoft.com/incident?action=viewrep&category=edb&release=2.00&type=f&incident=2645

<< I didn't ask, but just checking EDBManager is defaulting to sensitive
currently. >>

I can't reproduce this - could you send me the CREATE TABLE or the table
itself ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

You've got to add reverse engineer for a single table into EDBManager.


  EXECUTE IMMEDIATE 'CREATE TABLE "Contacts"
                    (
                    "_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
                    "_Title" VARCHAR(4) COLLATE "ANSI_CI",
                    "_Forename" VARCHAR(15) COLLATE "ANSI_CI",
                    "_Surname" VARCHAR(30) COLLATE "ANSI_CI",
                    "_Soundex" VARCHAR(5) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_Address1" VARCHAR(35) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_Address2" VARCHAR(35) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_Town" VARCHAR(35) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_County" VARCHAR(35) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_Country" VARCHAR(35) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_PostCode" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_HomePhone" VARCHAR(25) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_LastDPADate" DATE,
                    "_LastDPACheckBy" VARCHAR(30) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_LastDPAAccessRequest" DATE,
                    "_LastDPASupplied" DATE,
                    "_Mobile" VARCHAR(25) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_HomeEmail" VARCHAR(60) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_LastRecruited" DATE,
                    "_LatestCVDate" DATE,
                    "_Placed" DATE,
                    "_fkJobCodes" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_fkMarkets" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_RecruitedBy" VARCHAR(3) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_WeeksBetweenContacts" INTEGER DEFAULT 12,
                    "_DaysBetweenAttempts" INTEGER DEFAULT 1,
                    "_ModDate" DATE,
                    "_Status" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_DoB" DATE,
                    "_YoB" SMALLINT,
                    "_LastCallBy" VARCHAR(3) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_Source" VARCHAR(20) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_fkUsers_Buddy" VARCHAR(5) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_BudyLastAttempt" DATE,
                    "_BuddyAlarm" TIMESTAMP,
                    "_PNotes" VARCHAR(35) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_NoMailShots" BOOLEAN DEFAULT FALSE ,
                    "_LatestCVType" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_BuddySleepUntil" DATE,
                    "_LastCall" DATE,
                    "_Created" DATE DEFAULT CURRENT_DATE,
                    "_Partner" VARCHAR(60) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_HiringManager" BOOLEAN DEFAULT FALSE ,
                    "_NoticePeriod" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_BaseSalary" VARCHAR(15) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_SalaryDate" DATE,
                    "_SalaryWanted" VARCHAR(15) COLLATE "ANSI_CI" DEFAULT ''''  NOT NULL,
                    "_WantedDate" DATE,
                    "_NoEshots" BOOLEAN DEFAULT FALSE ,
                    "_PackageInfo" CLOB COLLATE "ANSI_CI",
                    "_Links" CLOB COLLATE "ANSI_CI",
                    "_Notes" CLOB COLLATE "ANSI_CI",
                    "_Skills" CLOB COLLATE "ANSI_CI",
                    "_BuddyNotes" CLOB COLLATE "ANSI_CI",
                    "_OtherEddresses" CLOB COLLATE "ANSI_CI",
                    "_DPAHistory" CLOB COLLATE "ANSI_CI",
                    "_Attributes" CLOB COLLATE "ANSI_CI",
                    "_UserFlags" CLOB COLLATE "ANSI_CI",
                    "_LatestCV" CLOB COLLATE "ANSI_CI",
                    "_SellingPoints" CLOB COLLATE "ANSI_CI",
                    "_EMailGroups" CLOB COLLATE "ANSI_CI",
                    "_OtherPhoneNumbers" CLOB COLLATE "ANSI_CI",
                    "_Referrals" CLOB COLLATE "ANSI_CI",
                    "_sTitle" VARCHAR(4) COLLATE "ANSI" COMPUTED ALWAYS AS IF(_Title IS NULL,'''',_Title),
                    "_sForename" VARCHAR(4) COLLATE "ANSI" COMPUTED ALWAYS AS IF(_Forename IS NULL,'''',_Forename),
                    "_sSurname" VARCHAR(4) COLLATE "ANSI" COMPUTED ALWAYS AS IF(_Surname IS NULL,'''',_Surname),
                    CONSTRAINT "PK" PRIMARY KEY ("_ID")
                    )
                    VERSION 1
                    UNENCRYPTED
                    INDEX PAGE SIZE 4096
                    BLOB BLOCK SIZE 512
                    MAX ROW BUFFER SIZE 32768
                    MAX INDEX BUFFER SIZE 65536
                    MAX BLOB BUFFER SIZE 32768';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "ContactName" ON "Contacts"
                    ("_Surname" COLLATE "ANSI_CI", "_Forename" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "HomeEmail" ON "Contacts"
                    ("_HomeEmail" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "Market" ON "Contacts"
                    ("_fkMarkets" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "JobCode" ON "Contacts"
                    ("_fkJobCodes" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "Buddies" ON "Contacts"
                    ("_fkUsers_Buddy" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "Town" ON "Contacts"
                    ("_Town" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "County" ON "Contacts"
                    ("_County" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "Country" ON "Contacts"
                    ("_Country" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "Soundex" ON "Contacts"
                    ("_Soundex" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE TEXT INDEX "fti_Skills" ON "Contacts"
                    ("_Skills" COLLATE "ANSI_CI")
                    INDEXED WORD LENGTH ''30''
                    WORD GENERATOR "wgLookupList"';
 
  EXECUTE IMMEDIATE 'CREATE TEXT INDEX "fti_Attributes" ON "Contacts"
                    ("_Attributes" COLLATE "ANSI_CI")
                    INDEXED WORD LENGTH ''30''
                    WORD GENERATOR "wgLookupList"';
 
  EXECUTE IMMEDIATE 'CREATE TEXT INDEX "fti_Notes" ON "Contacts"
                    ("_Notes" COLLATE "ANSI_CI")
                    INDEXED WORD LENGTH ''30''
                    WORD GENERATOR "wgStandard"';


Roy Lambert

Roy,

<< You've got to add reverse engineer for a single table into EDBManager. >>

Yes, I'll get to it when I get to it, and there's about 200 things in front
of it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

Okay, there is an issue here, but it's only an issue when you create the
computed column with a length too small to fit the calculation.  In your
case, you're defining it with a length of 4, when the columns in the
calculation are 15 (ForeName) and 30 (SurName ) in length.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Okay, there is an issue here, but it's only an issue when you create the
>computed column with a length too small to fit the calculation. In your
>case, you're defining it with a length of 4, when the columns in the
>calculation are 15 (ForeName) and 30 (SurName ) in length.

Its that helpful feature again filling in the info for me. It makes it very easy to miss things unless you've developed a habit of checking all the bits out.

Maybe item 201 could be a flag to turn it on/off.

Roy Lambert

Roy

>Its that helpful feature again filling in the info for me. It makes it very easy to miss things unless you've developed a habit of checking all the bits out.
>
>Maybe item 201 could be a flag to turn it on/off.

Forget that - I just remembered I had to do them via SQL cost EDBManager won't do it - as per the other thread - so its all my fault for just altering the field names and forgetting the size.

Roy Lambert