Formula Error

Question

I have a formula in my column that only extracts numbers between dashes. The problem is when i take all my data filters off OBIEE, it does not provide me results. I need to tell the formula that is there is no text between the dashes then put a zero. I feel this would provide me results, but at the current moment I have hit a barrier. My formula is below. Any suggestions. Im very green to OBIEE.

FORMULA: Case when length(SUBSTRING(SUBSTRING("Service Request Attributes"."Problem Code" from Locate('-',"Service Request Attributes"."Problem Code")+2 for LENGTH ("Service Request Attributes"."Problem Code"))FROM 1 FOR -4+ LOCATE('-', SUBSTRING("Service Request Attributes"."Problem Code" from Locate('-',"Service Request Attributes"."Problem Code")+2 for LENGTH ("Service Request Attributes"."Problem Code")))))=0 then 0 end

Thomas Dodds · Answer

Parse out these items in the physical column mappings into their own logical columns ...

benefits:

* let the database (strongest worker) do this work and not the OBIS or OBIPS machines
* build once - reuse many

Robert Angel · Answer

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)

Rigo · Answer

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:

Problem Category

"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?