Null and concat

[Bart Pietercil]

Hi Thorsten,
On 5-okt-07, at 14:38, Thorsten Hohage wrote:

> Hi Bart,
>
> On 2007-10-05, at 14:03, Bart Pietercil wrote:
>
>> SELECT CONCAT('Va’, NULL, ‘na’);
>>
>> crashes VStudio
>
> no - I got a "unexpected token NULL"
>
>> SELECT CONCAT('Va’, NULL)
>> I get unexpected token NULL.
>
> same here and IMHO this is ok, because NOBODY would type a constant  
> NULL in a concat statement.
>


call me NOBODY

I have defined a unique_test method(field) in a table.
Table has fields

     aTable = CreateTable("tbl_typelists",EVTableKind.kTblPermanent)

     VarCharField = aTable.CreateVarCharField("typelistname", 
512,EVFlag.fIndexed)
     VarCharField = aTable.CreateVarCharField("created_by", 
250,EVFlag.fIndexed)

     DateTimeField = aTable.CreateDateTimeField("creation_dt")

     PtrField = aTable.CreateObjectPtrField 
("objptr_organisation",Table 
("tbl_organisations"),EVOnDelete.kCascade,EVFlag.fIndexed 
+EVFlag.fNullable,"typelist_for_organisation")
    VarCharField = aTable.CreateVarCharField 
("unique_name_organisation",1022,EVFlag.fIndexed 
+EVFlag.fUnique,"CONCAT(typelistname, objptr_organisation)")


It is this last one that is giving me trouble

Since objptr_organisation can be NULL my method in  
unique_name_organisation returns in that case a NULL value. I would  
have preferred it would return the typelistname, but I can understand  
I returns null (because a param is null).

so I call my stored procedure

create or replace procedure cm_spBuildUserTypes()
begin
DECLARE mylastrecid Long;
insert into tbl_typelists(created_by,typelistname,creation_dt) values  
('cm_spBuildUserTypes','usertypes',NOW());
set mylastrecid = select recid FROM tbl_typelists where typelistname  
= 'usertypes';
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('active',1,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('deactivated',2,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('blocked',3,mylastrecid);
exception
when others then
call cm_CoreErrorHandler();
end;

no problem (but mind you objptr_organisation is NULL, so  
unique_name_organisation is also NULL)

now I call second procedure

create or replace procedure cm_spBuildUserRoles()
begin
DECLARE mylastrecid Long;
insert into tbl_typelists(created_by,typelistname) values  
('cm_spBuildUserRoles','userroles');
set mylastrecid = select Last_RecID();
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('Coachee',1,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('Coach',2,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('Administrator',3,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('Tutor Administrator',4,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('Tutor Editor',5,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('Tutor Reviewer',6,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('External Trainer',7,mylastrecid);
insert into tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
values ('Competence Editor',8,mylastrecid);
exception
when others then
call cm_CoreErrorHandler();
end;


This now fails with unique constraint violation because also for this  
list the objptr_organisation = NULL so unique_name_organisation = NULL.

Now the strange thing is I asked yesterday Ruslan specificly about  
NULL values in UNIQUES and he assured me that NULLs are excluded from  
the index.
So now I wonder why I am getting this message about unique constrain  
violation.

Mind you at this point there is 1 record in the table (the first list  
created by the first procedure)


Any insight welcome


Bart