Plus sign operator in Calculated Field using Simple Formula

Nick shared this question 3 months ago
Answered

Hi team,

When using the plus sign in calculated field using simple formula, let's say we are using MS SQL, I understand if:

string + string = "string string" (concatenates if string values)

n + n = 2n (addition if numeric fields)

Would the same apply if Oracle DB is used as the underlying DB? It doesn't seem to be the case from what I see (7.4).

Thanks,

Nick

Comments (5)

photo
1

Hi Nick,


I will have to test this, I know Oracle handles some concatenation functions differently. If you can let me know what specific outcome you are looking for I can help provide the query you need for this.


Cheers,

Neal

photo
1

Hi Neal,

Hope you are well.

Just wanted to confirm if you were seeing the same thing with the + sign operator when the underlying db is mssql vs oracle db for the following calcs below.

So in the case of mssql where you have:

char_field + char_field, I expect the result to be "<char_field value><char_field_value>"

num_field + num_field, I expect the result to be the addition of both fields

Whereas, in Oracle, I'm getting "ORA error invalid numeric field" for both scenarios above using the exact same calculations

Thanks,

Nick

photo
1

Hi Nick,


I have tested this on my Oracle instance, and the char_field + char_field does result in that issue, but the num_field + num_field for mine works as expected. The reason for this not working is just down to the way Oracle handles the "+" operator compared to MS SQL. To achieve the same function for the char_fields, please use a Freehand SQL type and use on of the following methods:


CONCAT(char_field, char_field2)

OR

char_field || ' ' || char_field2


The number field seems odd though. Would you be able to send through some more specifics on the fields, their underlying types and direct example?


Cheers,

Neal

photo
1

Hi Neal,

Thanks for the confirmation.

For the numeric field, no need to worry about it. I was able to sort it out from my end being mainly due to improper use of the + sign operator as well.

Enjoy the week ahead :)

Thanks,

Nick

photo
1

Hi Nick,


Great to hear. Enjoy your week as well.


Cheers,

Neal