I am not sure exactly what you are trying to achieve, but have you tried adding an IfNull around the value that is text numeric, to default it to a format that your formula will presumably translate to zero?
Btw, if I am understanding you correctly and you are just after getting a number from between marks, like this
Then why not just use replace; -
Which in your case is; - replace(IfNull("Service Request Attributes"."Problem Code",0),'-','')
Or is your requirement more complex than that?
Sorry I am completely Green to OBIEE so thanks for your patients in advance. Ok so what I am really doing is going into a problem category column that has text and numbers for example:
"Fix/Tubing/Conection - 369 - Tubing/Fitting".
"Conecct - 659687 - Fit"
Problem code Column the result is
row 1 "369"
row 2 659687
row 3 etc
So if I am understanding you correctly if I just use the formula you stated - replace(IfNull("Service Request Attributes"."Problem Code",0),'-','') it should work rather than have the whole complex formula I put earlier?
No, sorry as your format is more complex, but you do need to add the IfNull(......,0) to your original; -
IfNull("Service Request Attributes"."Problem Code",0)
Parse out these items in the physical column mappings into their own logical columns ...
- let the database (strongest worker) do this work and not the OBIS or OBIPS machines
- build once - reuse many