select query on uvsh, involving nulls
Posted: Sat Jan 03, 2009 8:23 am
I have a select query on uvsh, which concatenates the different fields and replaces any @VM and @TM
This code works fine, when there aren't any nullables in the fields. When any of the fields is null, the output is null (offcourse thats the funcationality of convert function)
So I applied the above null check condition to each of the fields, if it is null, then the output is empty string else it is the same field.
But, whenever Field2 is null, Field3 doesn't get displayed irrespective of whether it is null or not. I do get the output though i.e. only Field1 gets displayed with field seperators until Field2.
I tried different approach using @NULL.STR(below), but it doesn't work either.
Code: Select all
select EVAL "CONVERT(@TM:@VM,'.', Field1:'-':Field2:'-':Field3) from H_TABLE;
So I applied the above null check condition to each of the fields, if it is null, then the output is empty string else it is the same field.
Code: Select all
select EVAL "CONVERT(@TM:@VM,'.', If Isnull(Field1) then '' else Field1:'-':If Isnull(Field2) then '' else Field2:'-':If Isnull(Field3) then '' else Field3) from H_TABLE;
I tried different approach using @NULL.STR(below), but it doesn't work either.
Code: Select all
select EVAL "CONVERT(@TM:@VM,@NULL.STR,'.', Field1:'-':Field2:'-':Field3) from H_TABLE;