select query on uvsh, involving nulls

[mystuff]

I have a select query on uvsh, which concatenates the different fields and replaces any @VM and @TM

Code: Select all

select EVAL "CONVERT(@TM:@VM,'.', Field1:'-':Field2:'-':Field3) from H_TABLE; 

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.

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; 

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,@NULL.STR,'.', Field1:'-':Field2:'-':Field3) from H_TABLE; 

[ray.wurlod]

Think about it. NULL in any operation (including concatenation) must return NULL.

Now re-think what you want to get out of this expression and post that specification.

[mystuff]

Think about it. NULL in any operation (including concatenation) must return NULL.

Now re-think what you want to get out of this expression and post that specification.

Shouldn't the below code handle the situation, because null is being replaced with empty string.

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 wanted to get the output as

Code: Select all

Field1-Field2-Field3

When Field2 is null (Field1 and Field3 are not nulls). I am expecting,

Code: Select all

Field1--Field3

But I get

Code: Select all

Field1-

[ray.wurlod]

Probably. For testing purposes can you output three extra columns that contain "Y" if the field is null and "N" otherwise to see what's consistent?

Not sure what your CONVERT is trying to achieve, perhaps leave that out of the mix also, or convert all mark characters to "printables", for example

Code: Select all

Convert(@FM:@VM:@SM:@TM,'~}|{',expression)

just to see what's actually happening in expression.