>>Hi everybody,
>>
>>I have the following replace statement:
>>replace Street with m.lcNewStreetName, ;
>> ccode with m.lcNewCode, ;
>> town with m.lcNewTown, ModiType with 'C', ;
>> (.cWorkField) with .cReplVal ;
>> for upper(ccode+town+Street+str(StNum,4)+StNumExt+Unit)= ;
>>m.lcCond in BldMstr
>>
>>Set exact is set to off, because I want to replace old street names with the new street names regardless of StNum or StNumExt. However, if StNumExt is NULL, this replace command doesn't work.
>>
>>We have situs index in the table, which is exactly
>>upper(ccode+town+Street+str(StNum,4)+StNumExt+Unit)
>>
>>Could you please advise, what should I do, to make the replace command work for NULL in Unit or StNumExt as well?
>>
>>Thanks a lot in advance.
>
>NULL values propagate in most calculations. The concatenation (+) involving a null value will produce NULL for the entire result.
>
>Perhaps you can convert NULL to an acceptable replacement with NVL(). For instance:
NVL(CharField4, space(4)). If the field is NULL, you will get space(4) instead.
>
>HTH, Hilmar.
Hi Hilmar,
This basically confirms my thoughts. I'm thinking about using this index expression instead:
upper(ccode+town+street+ ;
nvl(str(StNum,4), space(4))+nvl(StNumExt, space(4))+nvl(Unit, space(6)))
If it's not broken, fix it until it is.
My Blog