PADR(NVL(i1,""),11) is wrong for .NULL. value
See message#
1407524 Agnes
>Use PADR(NVL(i1,""),11) to make it work correctly for NULL values.
>
>>Hi All,
>>
>>this is for your information, you might confirm as well.
>>
>>Version mentioned is just the highest, I can follow the problem down to VFP 7.0
>>
>>Following construct:
>>
>>CREATE CURSOR curTest (i1 I NULL)
>>
>>INSERT INTO curTest VALUES (.NULL.)
>>delete
>>INSERT INTO curTest VALUES (1)
>>INSERT INTO curTest VALUES (20)
>>
>>SELECT PADR(i1,11) FROM curTest
>>
>>Result is
>>
>>1
>>2
>>
>>Table structure of result shows that the result contains one char field of lenght 1 (instead of 11)
>>
>>Some experiments with WHERE, NVL, JOIN allways return the same.
>>
>>It does not matter if the record is deleted or not, only factor is that it is the first one.
>>
>>Update
>>Other approaches like CAST or BINTOC will solve my problem (I need a character version of a interger surrogate key for a combobox)
>>
>>Agnes
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]