to_decimal function in BODS 14.0 and 14.02 gave different results

[Former Member]

I have table with column1 and column2. I would like to add new column (for example column3 name) in BODS using definition:

table.column1*100

+to_decimal( nvl(table.column2,'0'),'','',0)

Column1 contain value 2011001100 and column2 is empty (contain space). In BODS 14.0 column3 contain 201100110000 but in BODS 14.2 I received NULL and I do not know why (definitions are the same in BODS 14.0 and 14.2).

[Former Member]

Hi ,

Please Cast() function, it may work.

Regards

aSGAR

[Former Member]

Hi Konard

i checked the function in my DS 14.2, for me it is working fine.

my question is if you are trying to add the column1 and column2 with nvl function applying then if the column 1 is varchar then it will show null value.

if you want to concatenate then try || (pipe) symbol instead of + sign. then even if the column1 is varchar it will give you the column1 value if the column2 is null.

Thanks

Prasannakumar

[Former Member]

Hi,

Column1 is decimal of course and column2 is varchar. That is why I would try convert to decimal. I could add:

table.column1*100

+to_decimal( nvl(ifthenelse(table.column2=' ','0',table.column2),'0'),'','',0)

but it is a workaround not a solution.